If not exist with Date SQL-CodePudding

i want to add the time, if there is free time as example im doing that with two textboxes where im entering the date

Database 20.04.2020 - 20.05.2020

so there should be no possibility to add a time, i guess? as example

16.04.2020 - 16.04.2020 or 22.04.2020 . 18.05.2020

my code right now is

If not exists (Select * From Praktikum where P_Start >= '"   tbZeit.Text   "' and P_Ende <= '"   tbZeit1.Text   "' ) Begin INSERT INTO Praktikum (P_Start, P_Ende ) VALUES ( '"   tbZeit.Text   "', '"   tbZeit1.Text   "' )End";

Outcome would be with tb1 16.04.2014 and tb2 16.06.2014

If not exists (Select * From Praktikum where P_Start >= '16.04.2014' and P_Ende < '16.06.2014' ) Begin INSERT INTO Praktikum (P_Start, P_Ende) VALUES ('16.04.2014', '16.06.2014') End

CodePudding user response：

Please use the date conversion to specific format for the date value and check

CodePudding user response：

I think what you are asking for is tests to reject where input from and to dates fall between existing dates or span them for example

drop table if exists t;

create table t
(id int auto_increment primary key, from_dt date,to_dt date);

insert into t (from_dt,to_dt) values ('20.04.2020' , '20.05.2020');



select * from t;
set @from = '2020-04-20';
set @to   = '2020-05-20';

delete from t where id > 1;

insert into t (from_dt,to_dt)
select @from,@to
where not exists
(select 1
from t 
where @from between from_dt and to_dt or
      @to   between from_dt and to_dt or
      (from_dt between @from and @end) or
      (to_dt between @from and @end) or
      (@from < from_dt and @to > to_dt)
);

select * from t;

And here's a place for you to play https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=597e9c3f22518252eb1415ec7ce407e1