Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Oracle - time format validation issue

Hi,
I have this query below where i want to check if a "CLOSE_TIME < OPEN_TIME"
SELECT ID,
  OPEN_TIME, 
  CLOSE_TIME
FROM CALENDAR 
where CLOSE_TIME < OPEN_TIME;

Open in new window


The problem i have is that the close and open time are VARCHAR2(4 CHAR) data type.

ex:
ID = 12345
OPEN_TIME  = 1800
CLOSE_TIME = 0000

a day finish at 2359. the day start at 0000 going up.

In this case close time is smaller than open time.

How can i update the query as the above query don't work.

Thank you
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Try this:
where to_date(close_time,'HH24MI') < to_date(open_time,'HH24MI')
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

For some reason, i dont get any results on the above example.

I also tried the oppisite and still no results
where to_date(close_time,'HH24MI') < to_date(open_time,'HH24MI')
where to_date(close_time,'HH24MI') > to_date(open_time,'HH24MI')
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

Thanks for your help. it's working