Oracle SQL: Extract Date & Time from Timestamp Timezone

Question

I have a START_DATE column in my table with values in the format YYYY-MM-DD HH:MM:SS AM/PM GMT. I'm trying to insert those values into another table but i'm getting a date format not recognized error. How do I fix this?

Sample SQL statement

INSERT INTO TABLE2 (
    DATE_WITH_TIMEZONE,
    DATE_WITHOUT_TIMEZONE
)
SELECT
    TO_TIMESTAMP(START_DATE, 'YYYY-MM-DD HH:MI:SS AM TZD'),
    TO_DATE(TO_TIMESTAMP(START_DATE, 'YYYY-MM-DD HH:MI:SS AM TZD'), 'YYYY-MM-DD HH:MI:SS AM')
FROM TABLE_DATE

Sample Data

2016-01-21 09:31:49 AM GMT
2016-02-22 06:37:32 PM GMT
2016-02-23 07:10:52 PM GMT
2016-03-15 08:54:40 PM GMT
2016-03-16 12:10:52 AM GMT

If it helps, these are the datatypes of the two columns in TABLE2

DATE_WITH_TIMEZONE TIMESTAMP WITH TIME ZONE

DATE_WITHOUT_TIMEZONE DATE

You know, you are missing a comma between to_timestamp and to_date — OldProgrammer, Aug 23 '17 at 14:50
@OldProgrammer missed when formatting the code in the question. Actual code includes the comma and I edited the question now. — Anish, Aug 23 '17 at 14:54
What is the data type of START_DATE in your source table, TABLE_DATE? Then: you have an input like 2016-01-21 09:31:49 AM GMT. You want to convert this to a date-time without time zone. Alright, what is the desired output? 2016-01-21 09:31:49 AM? Or do you need to CONVERT it first, for example to the "local" time zone of your database server? Don't take this question lightly; if the request to insert this data into the second table came from a business line, ASK THEM what date-time (with no time zone information) they need in that column. — , Aug 23 '17 at 15:50

MT0 · Accepted Answer · 2017-08-23T15:30:35.447

I have a START_DATE column in my table with values in the format YYYY-MM-DD HH:MM:SS AM/PM GMT

Assuming that your START_DATE column is of the DATE data type then your statement is incorrect; a DATE column has no format and it is stored internally as 7-bytes. It is only when it is passed to the user interface you are using to access the database that that UI will format the date (and not the database). SQL/Plus and SQL developer will format the date using the NLS_DATE_FORMAT session parameter (which is set per user session and can be changed) so relying on this format can lead to "interesting" bugs where the code you are using does not change but will start and stop working for different users depending on their session settings.

You can just do:

INSERT INTO TABLE2 (
    DATE_WITH_TIMEZONE,
    DATE_WITHOUT_TIMEZONE
)
SELECT FROM_TZ( CAST( START_DATE AS TIMESTAMP ), 'GMT' ),
       START_DATE
FROM   TABLE_DATE;

If your START_DATE column is of a string datatype (why would you do this?) then you will need to convert it to the appropriate type:

INSERT INTO TABLE2 (
    DATE_WITH_TIMEZONE,
    DATE_WITHOUT_TIMEZONE
)
SELECT TO_TIMESTAMP_TZ( START_DATE, 'YYYY-MM-DD HH12:MI:SS AM TZR' ),
       CAST( TO_TIMESTAMP_TZ( START_DATE, 'YYYY-MM-DD HH12:MI:SS AM TZR' ) AS DATE )
FROM   TABLE_DATE;

Oracle SQL: Extract Date & Time from Timestamp Timezone

1 Answers1