Oracle String to Date to Timestamp

Question

I have a date that is saved as 1900-01-01T00:00:00.000Z string Format. I am trying to convert it, but 1900 is changed to 2000. How to convert into timestamp without losing the year?

SELECT TO_TIMESTAMP(TO_CHAR(TO_DATE(SUBSTR('1900-01-01T13:00:00.000Z',0,10),'RRRR-MM-DD')),'DD/MM/RRRR HH24:MI:SSXFF') 
  FROM dual;

Show use your code attempt. (I can't read tha ttiny image text.) — jarlh, Sep 30 '21 at 12:49
Please do **not** post code as images. See here for more details why: http://meta.stackoverflow.com/questions/285551 — , Sep 30 '21 at 13:01

score 2 · Answer 1 · answered Sep 30 '21 at 13:20

You have two issues. The first is all of the unnecessary, extra conversions, addressed by the other respondents. The second, and the one that goes directly to the heart of your posted question, is getting the result in the wrong century. This is a result of your use of the RRRR formatting in your to_date/to_char/to_timestamp functions. RRRR (and RR) were meant to be temporary options to buy time in resolving the Y2k problem. They create a sliding window, where the century is assumed based on the supplied year. As it turns out, use of the RR / RRRR formats simply recreates Y2k, twenty years later. Quit using those formats, always use YYYY and always deal with 4-digit years. I and millions of my colleagues spent too much of our time in 1998-1999 just to see that work frittered away.

Barbaros Özhan · Answer 2 · 2021-09-30T13:07:41.720

1

Enough to use TO_TIMESTAMP conversion along with double quotes for the letters T and Z such as

SELECT TO_TIMESTAMP('1900-01-01T13:00:00.000Z','YYYY-MM-DD"T"HH24:MI:SS.FF"Z"') AS ts
  FROM dual

TS
01/01/1900 13:00:00,000000000

Considering the hour portion as 13 as seen from the image. Display style varies due to your current NLS settings of the database related to the time and the date

edited Sep 30 '21 at 13:07

answered Sep 30 '21 at 13:00

Barbaros Özhan

59,113
10
31
55

score 1 · Answer 3 · answered Sep 30 '21 at 13:00

Without more details, an option ( avoiding the T and the Z )

SQL> alter session set nls_timestamp_format='YYYY-MM-DDHH24:MI:SS.FF3' ;

Session altered.

SQL> select  to_timestamp(replace(replace('1900-01-01T00:00:00.000Z','T',''),'Z',''),'RRRR-MM-DDHH24:MI:SS.FF3') from dual ;

TO_TIMESTAMP(REPLACE(REPLACE('1900-01-01T00:00:00.000Z','T',''),'Z',''),'RR
---------------------------------------------------------------------------
1900-01-0100:00:00.000

If you want to keep them

SQL> select to_timestamp('1900-01-01T00:00:00.000Z','RRRR-MM-DD"T"HH24:MI:SS.FF3"Z"') from dual ;

TO_TIMESTAMP('1900-01-01T00:00:00.000Z','RRRR-MM-DD"T"HH24:MI:SS.FF3"Z"')
---------------------------------------------------------------------------
1900-01-0100:00:00.000

Oracle String to Date to Timestamp

3 Answers3