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;

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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

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