Convert Timestamp to Date in Oracle SQL

Convert timestamp to date in Oracle SQL

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Oracle convert Timestamp to Date

In Oracle, a date data type is always stores as 7-bytes consisting of century, year-of-century, month, day, hours, minutes and seconds; so, asking to convert something that has year-to-seconds components to a date does not require you to do anything:

SELECT date_registered AS reg_date FROM employee

Now, if you are storing date_registered as a string data type rather than as a date data type then you need to convert using TO_DATE:

SELECT TO_DATE( date_registered, 'YYYY-MM-DD"T"HH24:MI:SS' ) AS reg_date
FROM   employee

However, you should not do this and you should fix the underlying problem that you are storing dates as strings and not dates. You can solve this by converting the data type of the column:

ALTER TABLE employee ADD ( date_registered2 DATE );
UPDATE employee
  SET date_registered2 = TO_DATE( date_registered, 'YYYY-MM-DD"T"HH24:MI:SS' );
ALTER TABLE employee DROP COLUMN date_registered;
ALTER TABLE employee RENAME COLUMN date_registered2 TO date_registered;

(Note: The queries below assume that you have "fixed" the table so that the date_registered column has the date data type; if you do not want to do this then you will need to include the TO_DATE conversion as well.)

If you want the output to be a date where the time component is truncated back to midnight then:

SELECT TRUNC( date_registered ) AS reg_date
FROM   employee

(Note: The displayed date will still have hours, minutes and seconds components but they will all be zero after being passed through TRUNC.)

If you want it in a specific format, without the time component, then you need to output it as a formatted string (since the date data type does not have an associated format) using TO_CHAR:

SELECT TO_CHAR( date_registered, 'DD/MM/YYYY' ) AS reg_date
FROM   employee

db<>fiddle here

oracle sql to convert timestamp to date

I believe you first need to convert it to timestamp with timezone ad then cast it to date:

select CAST(to_timestamp_tz (dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM' ) AS DATE) 
from test

here is a demo

This will turn your data into specific timezone :

select 
to_timestamp_tz(dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM')at time zone 'Europe/Moscow'
from test

Then you can cast that :

select 
CAST
(to_timestamp_tz(dat_col, 'MM/DD/YYYY HH12:MI:SS AM TZH:TZM')at time zone 'Europe/Moscow'
AS DATE) 
from test

Please provide your expected results for more accurate code...

Not able to convert timestamp to char or date in oracle sql

'19-01-21 09:15:00.000000 PM' is a string literal; it is not a DATE or TIMESTAMP data type.

Since your string has fractional seconds, you can use TO_TIMESTAMP to convert it to a TIMESTAMP data type:

SELECT TO_TIMESTAMP( '19-01-21 09:15:00.000000 PM', 'DD-MM-RR HH12:MI:SS.FF6 AM' )
         AS timestamp_value
FROM   DUAL;

Which outputs:

| TIMESTAMP_VALUE               |
| :---------------------------- |
| 2021-01-19 21:15:00.000000000 |

---

If you want the value as a DATE data type then you can take the previous output and CAST it to a DATE:

SELECT CAST(
         TO_TIMESTAMP( '19-01-21 09:15:00.000000 PM', 'DD-MM-RR HH12:MI:SS.FF6 AM' )
         AS DATE
       ) AS date_value
FROM   DUAL;

or, if the fractional seconds are always going to be zero:

SELECT TO_DATE( '19-01-21 09:15:00.000000 PM', 'DD-MM-RR HH12:MI:SS".000000" AM' )
         AS date_value
FROM   DUAL;

Which both output:

| DATE_VALUE          |
| :------------------ |
| 2021-01-19 21:15:00 |

db<>fiddle here

---

I need to convert timestamp value to date/varchar value.

If you want to format the value as YYYY/MM/DD HH24:MI:SS then you can use TO_TIMESTAMP and then TO_CHAR:

SELECT TO_CHAR(
         TO_TIMESTAMP( '19-01-21 09:15:00.000000 PM', 'DD-MM-RR HH12:MI:SS.FF6 AM' ),
         'YYYY/MM/DD HH24:MI:SS'
       ) AS date_string
FROM   DUAL;

Which outputs:

| DATE_STRING         |
| :------------------ |
| 2021/01/19 21:15:00 |

(Note: this outputs a string data type and not a DATE data type; if you want a DATE data type then use the 2nd or 3rd example.)

db<>fiddle here

---

Related Topics