CODEWITHSUNDEEP
oracle-databaseoracle12csql-timestamp
Biswajit Buragohain
Biswajit Buragohain

Reputation: 47

VARCHAR2 to TIMESTAMP conversion in ORACLE 12c

I have a column in table A as

select create_time from table_a;

The value is

08-MAR-19 08.23.47.897000000 PM GMT.

This column has been marked as VARCHAR2 for some business purpose. Now I am trying to get this column and convert the value to TIMESTAMP for some purpose, like this as below:

SELECT TO_TIMESTAMP(create_time, 'DD-MON-YYYY HH.MI.SS.FF AM') from table_a;

But I am getting error:

ORA-01830: date format picture ends before converting entire input string

Can someone help me to convert this varchar data to timestamp. The reason I am trying to do is, I need to convert this time from one timezone to another : eg:

SELECT FROM_TZ(TO_TIMESTAMP(create_time, 'DD-MON-YYYY HH.MI.SS.FF AM'), 'UTC') AT TIME ZONE 'CET' from table_a;

Upvotes: 0

Views: 956

Answers (5)

Alex Poole
Alex Poole

Reputation: 191275

Doing something like:

to_timestamp(replace(create_time ,'GMT', null))

relies on your NLS settings, both for the timestamp format - particularly that it has an RR year mask - and the language for the month abbreviation. It would be safer to do:

to_timestamp(replace(create_time, ' GMT', null),
  'DD-MON-RR HH.MI.SS.FF AM', 'NLS_DATE_LANGUAGE=ENGLISH')

If the time zone isn't always GMT, but is always a valid and recognised region (not BST, for instance) then you might want to preserve the full date/time including that zone:

to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
  'NLS_DATE_LANGUAGE=ENGLISH')

If you want that as a plain timestamp you can cast it, possibly changing to a specific zone first:

cast(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
  'NLS_DATE_LANGUAGE=ENGLISH') as timestamp)

or

cast(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
  'NLS_DATE_LANGUAGE=ENGLISH') at time zone 'Asia/Tokyo' as timestamp)

or normalised to UTC (which won't affect GMT values, of course, as they're essentially the same):

sys_extract_utc(to_timestamp_tz(create_time, 'DD-MON-RR HH.MI.SS.FF AM TZR',
  'NLS_DATE_LANGUAGE=ENGLISH'))

db<>fiddle

Upvotes: 0

Atif
Atif

Reputation: 2210

Try this.

select  TO_TIMESTAMP( REPLACE(ts, 'GMT', '')) from test_timestamp;

Upvotes: 1

Lars Skaug
Lars Skaug

Reputation: 1386

The four characters GMT at the end are not accounted for in your format string

SELECT TO_TIMESTAMP('08-MAR-19 08.23.47.897000000 PM GMT', 'DD-MON-YYYY HH.MI.SS.FF AM') from dual;

The following takes care of that:

SELECT TO_TIMESTAMP(substr(create_time, 1, LENGTH(create_time) -4), 'DD-MON-YYYY HH.MI.SS.FF AM') t from dual;

Upvotes: 1

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

SQL> select replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','') AS RESULT from dual 
  ;

RESULT
--------------------------------
08-MAR-19 08.23.47.897000000 PM

SQL> select to_timestamp(replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','')) as RESULT from dual ;

RESULT
---------------------------------------------------------------------------
08-MAR-19 08.23.47.897000000 PM

SQL> select from_tz(to_timestamp(replace('08-MAR-19 08.23.47.897000000 PM GMT','GMT','')),'UTC') AT TIME ZONE 'CET' AS RESULT from dual ;

RESULT
---------------------------------------------------------------------------
08-MAR-19 09.23.47.897000000 PM CET

SQL>

Upvotes: 1

ismetguzelgun
ismetguzelgun

Reputation: 1105

Remove GMT using REPLACE

WITH A AS (SELECT REPLACE('08-MAR-19 08.23.47.897000000 PM GMT','GMT','') AS D FROM DUAL)
SELECT TO_TIMESTAMP (D, 'DD-MON-YYYY HH.MI.SS.FF AM')
  FROM A

Upvotes: 0

Related Questions