asked on

Oracle Date comparison by concatenating 2 fields

Hi

I am trying to do a date comparison in an SQL select statement on Oracle 9i. The problem in this particular case is that the date is stored in a field only containing the date and the time is stored in a field with a zero date e.g. "00/00/00 12:01:01".

This is basically what I am trying to do:
...
AND G16.DESPDATE >= to_date('27/03/2007 17:00','DD/MM/YYYY HH24:MI')
AND G16.DESPDATE <= to_date('28/03/2007 23:45','DD/MM/YYYY HH24:MI')
...

G16.DESPDATE as I said contains only the date
G16.DESPTIME however contains the zero date AND the time value.

Does anyone know how I can go about concatenating the 2 fields to do this comparison?

Any help would be massively appreciated, I have already spent AGES on this!

Guy Hengel [angelIII / a3]

what data type are the 2 fields DEPDATE and DESPTIME ?

wheelibin

ASKER

Hi, they are both DATE fields

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wheelibin

ASKER

Thanks for your quick answer.....your example gives me "Invalid Number" which can only be because of the zero date. If I select this "to_char( G16.DESPTIME , 'HH24:MI:SS')," it returns "00:00:00".

I have solved it by using your code but adding 366 to G16.DESPTIME (which should make the date 01/01/01).

This is now working:
AND G16.DESPDATE >= to_date('27/03/2007','DD/MM/YYYY')
AND G16.DESPDATE <= to_date('28/03/2007','DD/MM/YYYY')
AND to_date( to_char( G16.DESPDATE, 'YYYY-MM-DD') || ' ' || to_char( G16.DESPTIME +366 , 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') >= to_date('27/03/2007 17:00','DD/MM/YYYY HH24:MI')
AND to_date( to_char( G16.DESPDATE, 'YYYY-MM-DD') || ' ' || to_char( G16.DESPTIME +366, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') <=to_date('28/03/2007 23:45','DD/MM/YYYY HH24:MI')

Thanks a lot for that