CODEWITHSUNDEEP
sqldatabaseoracle-database
TraderJoeChicago
TraderJoeChicago

Reputation: 6315

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

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!

Upvotes: 7

Views: 21177

Answers (4)

Marcelo Todaro
Marcelo Todaro

Reputation: 1

For whoever is looking for a simple solution for returning only date without time, I found this:

to_date(SYSDATE,'dd/mm/yyyy')

Works like a charm. ;-)

Upvotes: 0

Gorgi
Gorgi

Reputation: 11

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

Upvotes: 0

psur
psur

Reputation: 4519

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'

Upvotes: 16

DCookie
DCookie

Reputation: 43533

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.

Upvotes: 8

Related Questions