extract date only from given timestamp in oracle sql

Question

The following query:

select cdate from rprt where cdate <= TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS') and ryg='R' and cnum='C002';

return: 2013/04/27-10:06:26:794 as stored in the table.

I want to get the date only as : 27-04-2013 and get the number of days between the resul tdate and sysdate.

SELECT to_date(create_date, 'DD-MM-YYYY') should work for you. Try it — ATR, May 14 '13 at 05:28
`ERROR at line 1: ORA-01861: literal does not match format string` pointing to cdate. — kumarprd, May 14 '13 at 05:34
sorry. Since you are already having date you need to convert it into char. So try to_char(create_date,'DD-MM-YYYY'). It should do job for you. — ATR, May 14 '13 at 05:45

score 26 · Answer 1 · edited Mar 08 '14 at 18:56

26

Use the function cast() to convert from timestamp to date

select to_char(cast(sysdate as date),'DD-MM-YYYY') from dual;

For more info of function cast oracle11g http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions016.htm#SQLRF51256

edited Mar 08 '14 at 18:56

BenMorel

34,448
50
182
322

answered Mar 07 '14 at 11:55

Fernando.

723
8
12

Piyush Dua · Answer 2 · 2020-08-13T15:29:37.130

In Oracle 11g, To get the complete date from the Timestamp, use this-

Select TRUNC(timestamp) FROM TABLE_NAME;

To get the Year from the Timestamp, use this-

Select EXTRACT(YEAR FROM TRUNC(timestamp)) from TABLE_NAME;

To get the Month from the Timestamp, use this-

Select EXTRACT(MONTH FROM TRUNC(timestamp)) from TABLE_NAME;

To get the Day from the Timestamp, use this-

Select EXTRACT(DAY FROM TRUNC(timestamp)) from TABLE_NAME;

mvp · Answer 3 · 2013-05-14T07:06:15.567

9

This is exactly what TO_DATE() is for: to convert timestamp to date.

Just use TO_DATE(sysdate) instead of TO_CHAR(sysdate, 'YYYY/MM/DD-HH24-MI-SS-SSSSS').

SQLFiddle demo

UPDATE:

Per your update, your cdate column is not real DATE or TIMESTAMP type, but VARCHAR2. It is not very good idea to use string types to keep dates. It is very inconvenient and slow to search, compare and do all other kinds of math on dates.

You should convert your cdate VARCHAR2 field into real TIMESTAMP. Assuming there are no other users for this field except for your code, you can convert cdate to timestamp as follows:

BEGIN TRANSACTION;
-- add new temp field tdate:
ALTER TABLE mytable ADD tdate TIMESTAMP;
-- save cdate to tdate while converting it:
UPDATE mytable SET tdate = to_date(cdate, 'YYYY-MM-DD HH24:MI:SS');

-- you may want to check contents of tdate before next step!!!

-- drop old field
ALTER TABLE mytable DROP COLUMN cdate;
-- rename tdate to cdate:
ALTER TABLE mytable RENAME COLUMN tdate TO cdate;
COMMIT;

SQLFiddle Demo

edited May 14 '13 at 07:06

answered May 14 '13 at 05:27

mvp

111,019
13
122
148

It throws error: `ERROR at line 1: ORA-01861: literal does not match format string` – kumarprd May 14 '13 at 05:31
Well, [SQLFiddle link](http://www.sqlfiddle.com/#!4/d41d8/10899) works fine for `sysdate`. What gives? Are you sure you are telling whole story here? – mvp May 14 '13 at 05:33
`SELECT to_date(cdate,'DD-MM-YYYY') from rprt where cdate <= TO_DATE(sysdate,'DD-MM-YYYY') and ryg='R' and cnum='C002';` give me that error – kumarprd May 14 '13 at 05:43
1

what is the data type of `cdate`? If it is `TIMESTAMP` or `DATE`, simple `TO_DATE(cdate)` will do. If it is string, you must use some format. But, you should simply convert it to real timestamp instead – mvp May 14 '13 at 05:48
Its VARCHAR2 datatype. How to convert it to real timestamp ?? – kumarprd May 14 '13 at 06:14
I have updated my answer to add an example on how to convert data type from `VARCHAR2` to `TIMESTAMP`. – mvp May 14 '13 at 07:07
3

you can NOT use to_date on a timestamp data type. – ionutioio Sep 17 '14 at 09:13
@ionutioio: you are most certainly wrong. Please take a look at linked SQLfiddle - it does use to_date() on timestamp field and it works just fine. Next time you should check before downvoting – mvp Sep 17 '14 at 16:29
@mvp, tried it myself yesterday (on oracle 11G) and for some reason it didn't work. The field was a timestamp with timezone. Sry for not checking the fiddle. – ionutioio Sep 18 '14 at 07:13
TO_DATE is for converting character data types to a DATE data type https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm – sample Oct 27 '16 at 19:36

score 2 · Answer 4 · answered May 14 '13 at 05:36

2

try this type of format:

SELECT to_char(sysdate,'dd-mm-rrrr') FROM dual

answered May 14 '13 at 05:36

Shailesh

980
8
16

score 1 · Answer 5 · edited Apr 16 '15 at 19:05

1

Convert Timestamp to Date as mentioned below, it will work for sure -

select TO_DATE(TO_CHAR(TO_TIMESTAMP ('2015-04-15 18:00:22.000', 'YYYY-MM-DD HH24:MI:SS.FF'),'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS') dt from dual

edited Apr 16 '15 at 19:05

rfornal

5,072
5
30
42

answered Apr 16 '15 at 18:43

AB Sikarwar

11
1

score 0 · Answer 6 · answered Nov 21 '17 at 18:46

0

If you want the value from your timestamp column to come back as a date datatype, use something like this:

select trunc(my_timestamp_column,'dd') as my_date_column from my_table;

answered Nov 21 '17 at 18:46

Frank Staheli

31
2

score -1 · Answer 7 · edited Jan 28 '16 at 10:41

-1

This format worked for me, for the mentioned date format i.e. MM/DD/YYYY

SELECT to_char(query_date,'MM/DD/YYYY') as query_date 
FROM QMS_INVOICE_TABLE;

edited Jan 28 '16 at 10:41

Arulkumar

12,966
14
47
68

answered Jan 28 '16 at 09:55

Tarun Verma

73
8

2

With this, you not have a date. You've a Varchar2 – Fernando. Jul 26 '17 at 20:47

extract date only from given timestamp in oracle sql

7 Answers7

Linked