Oracle: Similar to sysdate but returning only time and only date

Question

I understand that Oracle sysdate returns the current date AND time. That's great for timestamp or datetime columns.

Now let's say I have a DATE only column. What keywords should I use on my insert query?

insert into myTable1(myDateOnlyColumn) values(???)

And let's say I have a TIME only column. What keywords should I use on my insert query?

 insert into myTable2(myTimeOnlyColumn) values(???)

Thanks!

Answer 1

To remove time from sysdate you can just write TRUNC(sysdate).

For example:

SELECT TRUNC(SYSDATE) "TODAY" FROM DUAL;

will give you:

TODAY                     
------------------------- 
'2012-10-02 00:00:00'     

Answer 2

There is no such thing as a DATE only column in Oracle. The DATE datatype stores date and time.

If you only care about the date, you can:

INSERT INTO tbl (dtCol) VALUES (TO_DATE('20110929','YYYYMMDD');

This leaves the time component at 00:00:00. You don't have to display it though.

If you're only interested in the time component, you still have a date stored in the column. You'll just have to handle that on output. For example:

SQL> CREATE TABLE dt (d DATE);

SQL> INSERT INTO dt VALUES (TO_DATE('1:164800','J:HH24MISS'));

1 row inserted

Showing the actual contents of the column reveals a date was inserted:

SQL> SELECT * FROM dt;

D
--------------------
0/0/0000 4:48:00 PM

Selecting only the time component from the column gives you the output you want:

SQL> SELECT TO_CHAR(d, 'HH24:MI:SS') d FROM dt;

D
--------
16:48:00

SQL> 

If you think you need only a time column, you'll want to make sure you always insert the same date component.