Split date field into time and day Oracle

Question

I have a table TIME_TABLE on Oracle 10.6. There is a field called Start_Date on this table.

Item       Start_Date
 1         3/13/2012 8:00:00 AM
 2         4/2/2012 1:30:31 PM

I want to take only time information from Start_Date field like that, '8:00:00 AM'. Because I want to determine the time interval

CASE
WHEN   START_DATE between TO_DATE ('07:00 AM','HH:MI:SS AM') and  TO_DATE('11:59',' HH:MI:SS AM') THEN 'TIME1'

But it does not work! How can I do this ?

Thanks

score 0 · Accepted Answer · answered Jun 17 '12 at 06:50

0

Try it like this:

CASE
WHEN to_char(start_date, 'hh24miss') BETWEEN '070000' AND '115900'

Note that this may prevent the DB from using indexes on start_date

answered Jun 17 '12 at 06:50

A.B.Cade

16,735
1
37
53

score 0 · Answer 2 · answered Jun 17 '12 at 22:40

Your original query was pretty close also - as @A.B.Cade mentioned about performance of indexes you could test the difference between it and:

SELECT CASE
       WHEN START_DATE BETWEEN TO_DATE (
                                TO_CHAR (START_DATE, 'MM/DD/YYYY')
                                || ' 07:00:00',
                                'MM/DD/YYYY HH24:MI:SS'
                            )
                        AND  TO_DATE (
                                 TO_CHAR (START_DATE, 'MM/DD/YYYY')
                                 || ' 11:59:00',
                                 'MM/DD/YYYY HH24:MI:SS'
                             )
       THEN
           'TIME1'
   END

Split date field into time and day Oracle

2 Answers2