VARCHAR2 to TIMESTAMP conversion in ORACLE 12c

Question

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;

The `GMT` at the end of your string is the problem. You need to `substring` your input to get rid of that. — Lars Skaug, Aug 11 '20 at 14:01
Why are you storing `create_time` as a string instead of as a proper timestamp? What business purpose requires the wrong data type and data integrity and performance issues that can cause? And are the values always GMT? (Are you sure it *is* actually a string, and that isn't just how your client is displaying a timestamp, via your NLS settings?) — Alex Poole, Aug 11 '20 at 18:35
Hi @AlexPoole, yes I know that this column is actually a varchar field. The reason is that the client does not want to loose out any info incase of wrong data. There is no performance issue, as this table has a archive table and the data is less than 100 records per run. The time value is receive in a different timezone ( product does not allow to change db timezone to the client timezone..) hence the issue :) — Biswajit Buragohain, Aug 12 '20 at 11:41

score 1 · Accepted Answer · answered Aug 11 '20 at 14:04

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>

score 1 · Answer 2 · answered Aug 11 '20 at 14:05

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;

score 1 · Answer 3 · answered Aug 11 '20 at 14:07

1

Try this.

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

answered Aug 11 '20 at 14:07

Atif

2,011
9
23

score 0 · Answer 4 · answered Aug 11 '20 at 14:02

0

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

answered Aug 11 '20 at 14:02

ismetguzelgun

1,090
8
16

score 0 · Answer 5 · answered Aug 11 '20 at 18:43

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

VARCHAR2 to TIMESTAMP conversion in ORACLE 12c

5 Answers5