Insert TIME into Oracle SQL, DATE field

Question

Is there anyway to insert only time (no date) into a SQL field of type 'Date'

I have tried inserting:

13:00

and

01:00

and

13:00pm

and

01:00pm

But keep getting the error:

Not A Valid Month

Solution

The problem is the literal. For date/time values, Oracle expects values in the form ddMMMyyyy. (Or, you can use the expression DATE '2001-01-01. Literal values are explained here.) There doesn't appear to be a default format for time without a date.

In other words, you can get the same error with cast('1/1/2001' as date).

Instead, use the to_date() function:

select to_date('10:00', 'hh:mi')
from dual

This gives you much more flexibility with formats.

Also, as Ben notes in the comments, this gives you the current date with the time specified.

Comments

[Ben]

This will insert into the table the current date and so you always have to remember to remove it afterwards. It's better to store it in an INTERVAL DAY TO SECOND or the number of seconds since midnight as in the dupe.

[Gordon Linoff]

@Ben . . . That is correct. Thank you for further clarification. I was only addressing why the error is occuring.