Converting ISO Date VARCHAR into DATE YYYY-MM-DD HH:MM:SS in oracle

Question

I have ISO Date format in oracle VARCHAR column in TABLE1, i have to insert this value into TABLE2 where i have column datatype as DATE.

For Eg 1:

VARCHAR String Date1 - 2018-03-23T00:00:00.000+00:00
I want to convert into date datatype as - MM-DD-YYYY

While using below query getting the error,

INSERT INTO DATE_TEMP (DATE_VALUE) VALUES  (TO_DATE('2018-03-23T00:00:00.000+00:00','DD-MM-YYYY'));

Error Code:

ORA-01861: literal does not match format string

For Eg 2: - VARCHAR String Dat2 - 2018-03-23T12:34:56.123+00:00

INSERT INTO DATE_TEMP (DATE_VALUE) VALUES TO_TIMESTAMP_TZ('2018-03-23T12:34:56.123+00:00', 'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM');

Error Code:

ORA-03001: unimplemented feature

You are missing the parentheses around the values. It should be `values ( to_timestamp_tz ... )` not `values to_timestamp_tz ....` — , Mar 23 '18 at 10:28

score 3 · Answer 1 · answered Mar 23 '18 at 10:09

3

If everything behind the date is "0" (no hours, minutes, ..., as your example suggests), then it is fairly simple:

insert into table2 (date_column)
select to_date(substr(date_column, 1, 10), 'yyyy-mm-dd')
  from table1

answered Mar 23 '18 at 10:09

Littlefoot

131,892
15
35
57

Thanks for the query, what if I have in following format 2018-03-23T15:16:09.132+00:00 – Wanna Coffee Mar 23 '18 at 10:14
You'd use TO_TIMESTAMP_TZ, as a_horse showed in his answer. – Littlefoot Mar 23 '18 at 10:23
When i execute this query it shows following error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" – Wanna Coffee Mar 23 '18 at 10:37
1

If the value is `2018-01-01T00:00:00+08:00` then this will ignore the time zone and insert the date as `2018-01-01` when, if you convert all the times to a consistent time zone, in UTC the time is actually `2017-12-31T16:00:00Z`. – MT0 Mar 23 '18 at 11:04

MT0 · Answer 2 · 2018-03-23T10:58:20.543

Use TO_TIMESTAMP_TZ( date_string, format_model ) to convert it to a TIMESTAMP WITH TIMEZONE data type then you can use AT TIME ZONE 'UTC' to convert all values to a common time zone (since you are using a DATE column and this does not store time zone information) and either use CAST( previous_value AS DATE ) to convert it explicitly to a DATE data type or allow Oracle to perform an implicit cast in the INSERT statement:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE date_temp ( date_value DATE );

Query 1:

INSERT INTO DATE_TEMP (
  DATE_VALUE
) VALUES(
  TO_TIMESTAMP_TZ(
    '2018-03-23T00:00:00.000+00:00',
    'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
  ) AT TIME ZONE 'UTC'
)

INSERT INTO DATE_TEMP (
  DATE_VALUE
) VALUES(
  TO_TIMESTAMP_TZ(
    '2018-03-23T12:34:56.123+01:00',     -- Note: Different time zone
    'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
  ) AT TIME ZONE 'UTC'
)

SELECT * FROM DATE_TEMP

Results:

|           DATE_VALUE |
|----------------------|
| 2018-03-23T00:00:00Z |
| 2018-03-23T11:34:56Z | -- Note: value has been converted to the UTC time zone

As an aside:

I want to convert into date datatype as - MM-DD-YYYY

A date does not have a format - it is stored internally to the database as 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.

If you just want it as a date and do not want a time component then you can either use the TRUNC( date_value ) function to truncate your value back to midnight.

Update

Is it possible to insert record from table 1 to table 2. By this way we don't want to pass the value manually.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE date_temp ( date_value DATE );

CREATE TABLE string_temp ( string_value VARCHAR2(50) );

INSERT INTO string_temp
SELECT '2018-03-23T00:00:00.000+00:00' FROM DUAL UNION ALL
SELECT '2018-03-23T12:34:56.123+01:00' FROM DUAL;

Query 1:

INSERT INTO DATE_TEMP ( DATE_VALUE )
SELECT TO_TIMESTAMP_TZ(
         string_value,
         'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM'
       ) AT TIME ZONE 'UTC'
FROM   string_temp


SELECT * FROM DATE_TEMP

Results:

|           DATE_VALUE |
|----------------------|
| 2018-03-23T00:00:00Z |
| 2018-03-23T11:34:56Z |

Thanks for your answer, Is it possible to insert record from table 1 to table 2. By this way we don't want to pass the value manually. Actually it has lakhs of records and impossible to pass hard coded date parameter to insert query. — Wanna Coffee, Mar 23 '18 at 10:49

score 2 · Answer 3 · 2018-03-23T10:30:34.397

2

Your string contains a time zone information, so you need to use to_timestamp_tz, not to_date

to_timestamp_tz('2018-03-23T00:00:00.000+00:00', 'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM')

That can be cast to a date if necessary.

Online example: http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=021438eeec836b441b7f23ca57ff5af4

edited Mar 23 '18 at 10:30

answered Mar 23 '18 at 10:18

2

`INSERT INTO DATE_TEMP VALUES( TO_TIMESTAMP_TZ( '2018-03-22T12:34:56.123+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM' ) );` would be implicitly cast to a date with a time at 12pm and ignore that the time zone is +01:00. Since the time zone is going to be lost, it would be better to change the time zone to one consistent zone before the cast to a date. – MT0 Mar 23 '18 at 10:50

Converting ISO Date VARCHAR into DATE YYYY-MM-DD HH:MM:SS in oracle

3 Answers3