Oracle Date Format Convert Hour-Minute to Interval and Disregard Year-Month-Day
- by dlite922
I need to compare an event's half-way midpoint between a start and stop time of day. Right now i'm converting the dates you see on the right, to HH:MM and the comparison works until midnight.
the query says: WHERE half BETWEEN pStart and pStop.
As you can see below, pStart and pStap have January 1st 2000 dates, this is because the year month day are not important to me...
Valid Data:
+-------+--------+-------+---------------------+---------------------+---------------------+
| half | pStart | pStop | half2 | pStart2 | pStop2 |
+-------+--------+-------+---------------------+---------------------+---------------------+
| 19:00 | 19:00 | 23:00 | 2012-11-04 19:00:00 | 2000-01-01 19:00:00 | 2000-01-01 23:00:00 |
| 20:00 | 19:00 | 23:00 | 2012-11-04 20:00:00 | 2000-01-01 19:00:00 | 2000-01-01 23:00:00 |
| 21:00 | 19:00 | 23:00 | 2012-11-04 21:00:00 | 2000-01-01 19:00:00 | 2000-01-01 23:00:00 |
| 23:00 | 20:00 | 23:00 | 2012-11-05 23:00:00 | 2000-01-01 20:00:00 | 2000-01-01 23:00:00 |
+-------+--------+-------+---------------------+---------------------+---------------------+
Now observe what happens when pStop is midnight or later...
Valid Data that breaks it:
+-------+--------+-------+---------------------+---------------------+---------------------+
| half | pStart | pStop | half2 | pStart2 | pStop2 |
+-------+--------+-------+---------------------+---------------------+---------------------+
| 23:00 | 22:00 | 00:00 | 2012-11-04 23:00:00 | 2000-01-01 22:00:00 | 2000-01-01 00:00:00 |
| 23:30 | 23:00 | 02:00 | 2012-11-05 23:30:00 | 2000-01-01 23:00:00 | 2000-01-01 02:00:00 |
+-------+--------+-------+---------------------+---------------------+---------------------+
Thus my where clause translates to:
WHERE 19:00 BETWEEN 22:00 AND 00:00 ...which returns false and I miss those two correct rows above.
Question:
Is there a way to show those dates as integer interval so that saying half BETWEEN pStart and pStop are correct?
I thought about adding 24 when pStop is less than pStart to make 00:00 into 24:00 but don't know an easy way to do that without long string concatenations and number conversions. This would solve the problem because pStart pStop difference will never be longer than 6 hours.
Note: (The Query is much more complex. It has other irrelevant date calculations, but the result are show above. DATE_FORMAT(%H:%i) is applied to the first three columns and no formatting to the last three)
Thanks for your help:
