Oracle: similar to sysdate, but returns only time and only date

Question

I understand that Oracle sysdate returns the current date and time. This is great for timestamp or datetime columns.

Now let's say that I only have a DATE column. What keywords should I use for my insert request?

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

And let them say that I only have a TIME column. What keywords should I use for my insert request?

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

Thanks!

Answer 1

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

If you only need a date, you can:

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

This leaves the time component at 00:00:00. You do not need to display it, though.

If you are only interested in the time component, you still have the date stored in the column. You just need to handle this in the output. For instance:

 SQL> CREATE TABLE dt (d DATE); SQL> INSERT INTO dt VALUES (TO_DATE('1:164800','J:HH24MISS')); 1 row inserted 

Displaying the actual contents of the column indicates that the date was inserted:

 SQL> SELECT * FROM dt; D -------------------- 0/0/0000 4:48:00 PM 

Selecting only the time component from the column gives the desired result:

 SQL> SELECT TO_CHAR(d, 'HH24:MI:SS') d FROM dt; D -------- 16:48:00 SQL> 

If you think that you only need a temporary column, you want you to always insert the same date component.

Answer 2

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

For instance:

 SELECT TRUNC(SYSDATE) "TODAY" FROM DUAL; 

will provide you with:

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

Answer 3

 select sysdate, to_date(to_char(sysdate, 'dd/mm/yyyy'),'dd/mm/yyyy') d from dual