query help

[pjsatif]

Hi, this is my first thread ...

I've a table with a Date column and a time column (and more columns of course...). So I'm searching for a query to limit the result by date and time.
For example I want to select all rows that contains date and time between 20/05/2007 at 05:00:00 and 21/05/2007 at 05:00:00.

I've tryed something like this: select * from table1 where date>='20/05/2007' and time>='05:00:00 ' and date<='21/05/2007' and time<='05:00:00'.

But, of course, the time interval will return only the time 05:00:00 :-( .... Anyone could, please, help me?

Many thanks

 

[PHV]

Doesn't your RDBMS support a DateTime data type ?
If so, then merge your Date and Time columns into a single DateTime column and your life will be easier.

Anyway, with the strange syntax you've used in your post:

SELECT * FROM table1
WHERE (date='20/05/2007' AND time>='05:00:00')
   OR (date='21/05/2007' AND time<='05:00:00')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pjsatif]

Many thanks for your help ... but if the dates are note adjacent? i.e between 20/05 and 30/05 :-( ... I'm using DB2 and I cannot change tables on this DB (It's corporative) ... Any other idea?

Many thanks

 

[PHV]

You may try this:

SELECT * FROM table1
WHERE (date='20/05/2007' AND time>='05:00:00')
   OR (date BETWEEN '21/05/2007' And '29/05/2007')
   OR (date='30/05/2007' AND time<='05:00:00')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pjsatif]

one more time many thanks for your support. Works ... but my idea it's something more flexible that allow substitute date and time values in query for variables ... in this case how I can add a day (date='20/05/2007' + 1)????

Regards

 

[JarlH]

To add one day using a literal:
[tt] date'2007-06-26' + '1' day[/tt]

To add one day using a variable:
[tt] date'2007-12-12' + cast(? as interval day)[/tt]