casting timestamp as date oracle

Question

I have this Timestamp field which looks like this: 2015-08-24 16:24:28.763915 and I want to get only the date and insert to date field

I tried this:

select
TO_DATE(CAST (CON1.AF_UPDATE_DT AS VARCHAR(10)), DD/MM/YYYY)
FROM AF_EMR_MEM_CONT CON1

but i get this error

00904. 00000 - "%s: invalid identifier"

If I try to do this sql:

select AF_UPDATE_DT
       TO_DATE(CAST (CON1.AF_UPDATE_DT AS VARCHAR(10)), YYYY/MM/DD)
FROM AF_EMR_MEM_CONT CON1

I get the error:

00923. 00000 - "FROM keyword not found where expected"

A `date` column still has a time part in Oracle. If you want to set the time to `00:00:00` use `trunc(AF_UPDATE_DT)` — , Sep 06 '15 at 08:47

score 1 · Answer 1 · answered Sep 06 '15 at 09:59

1

You can just truncate the TIMESTAMP, result of the TRUNC function is DATE:

SELECT TRUNC(LOCALTIMESTAMP) FROM DUAL;

answered Sep 06 '15 at 09:59

Husqvik

score 0 · Answer 2 · answered Sep 06 '15 at 07:47

0

You can use SUBSTR to extract the date portion from the string, and then TO_DATE to modify it to a date object:

select TO_DATE(
  SUBSTR('2015-08-24 16:24:28.763915', 1, 10), 'YYYY-MM-DD') -- 2015-08-24
from DUAL

See this demo

answered Sep 06 '15 at 07:47

Nir Alfasi

Thank you I tried your solution and it worked great! – YossiH Sep 06 '15 at 11:27

2 Answers2