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

Question

I need to convert timestamp value(19-01-21 09:15:00.000000 PM) to date format('YYYY/MM/DD HH24:MI:SS') . I'm trying to do it using to_char function (to convert it to varchar and use it in TO_DATE function). I'm getting below error in to_char function. Is this the feasible way or please suggest if anything better works

select TO_CHAR('19-01-21 09:15:00.000000 PM','DD-MM-YY HH:MI:SSxFF AM') from dual;

select TO_DATE(TO_CHAR('19-01-21 09:15:00.000000 PM','DD-MM-YYYY HH:MI:SSxFF AM'),'YYYY/MM/DD HH24:MI:SS'  ) from dual;

I'm getting below error:

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

Maybe you need to put YY instead of YYYY in TO_CHAR('19-01-21 09:15:00.000000 PM','DD-MM-YYYY HH:MI:SSxFF AM')? — Fabio Mendes Soares
@FabioMendesSoares There is only a 2-digit year; if you use YYYY then it will be matched as 0021 not 2021. In the string-to-date conversion rules documentation you can see that that is not necessary even if a 4-digit year was given. The problem is that TO_CHAR expects a DATE or TIMESTAMP value as the first argument but a string literal is being given and Oracle is implicitly casting the string to a relevant data type using the NLS session parameters. — MT0
And as a side observation - does the term 'Y2k bug' mean anything to you? — EdStevens
@MT0 OK, thanks for the explanation, I just saw in the first call to TO_CHAR YY was passed for the year, and in the second time inside function TO_DATE YYYY was specified. — Fabio Mendes Soares

MT0 MT0 · Accepted Answer · 2021-01-19T14:16:25

'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

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

1 Answers