topic Re: SQL query for date & time interval in Application Development and Automation Discussions

SQL query for date & time interval

Former Member — Mon, 31 May 2010 13:43:59 GMT

Hello everyone,

there is my problem

I have to make a search for events that are taking place during inserted date & time .

There's the table structure:

event

datefrom

dateto

timefrom

timeto

...

The problem is, when I try:

select * from xyz where datefrom <= inserted_date and dateto >= inserted_date and timefrom <= inserted_time and timeto >= inserted time;

then I don't get back all records.

eg: user inserts date = 10.10.2010 and time 10:00 and in the table is event [abc 11:00 9.10.2010 -> 09:00 11.10.2010] stored following way:

event abdc

datefrom 09.10.2010

dateto 11.10.2010

timefrom 11:00

timeto 09:00

...

=> this event won't be found, because the sql logic compares only single parameters and not their context.

Do you have any idea, how to solve this issue?

Thank you.

Lukas

Re: SQL query for date & time interval

hubert_heitzer — Mon, 31 May 2010 14:06:28 GMT

Hi Lukas,

how about

select * from xyz where inserted_date between datefrom and dateto and inserted_time between timefrom and timeto;

Regards, Hubert

Edited by: Hubert Heitzer on May 31, 2010 4:06 PM

Re: SQL query for date & time interval

Former Member — Mon, 31 May 2010 15:02:34 GMT

Hello Hubert,

thank you very much for your proposal, but your code makes the same select as mine (but in a more user friendly look :).

Table:

datefrom 09.10.2010

dateto 11.10.2010

timefrom 11:00

timeto 09:00

Event date = 10.10.2010 and time 10:00.

Your query:

select * from xyz where 10.10.2010 between 09.10.2010 and 11.10.2010 (that's so far ok) and 10:00 between 11:00 and 9:00 (no return there, because 10:00 doesn't lie within interval 11:00 -> 9:00... actually no value lies there) ;

Don't you have any other idea?

Thank you

Lukas

Re: SQL query for date & time interval

ThomasZloch — Mon, 31 May 2010 15:21:10 GMT

Try something like:

( date > datefrom or ( date = datefrom and time >= timefrom ) ) and

( date < dateto or ( date = dateto and time <= timeto ) )

Thomas

Re: SQL query for date & time interval

Former Member — Mon, 31 May 2010 18:54:23 GMT

Hi Thomas,

thanks for proposal, it seems that this should work! Stupid me that I haven't got this idea.

Kind Regards

Lukas