How convert time in query and add time zone - date format not recognized

Question

I need to add time zone to what is returned by Oracle SQL query.

SELECT
    mw.appdate APPDATE
FROM
    mwo mw

This returns:

APPDATE
23-SEP-2021 00:00:00

I was looking at ISO-8601 select to_char, but I'm not sure how to apply this to my query results.

SELECT
to_char(mw.appdate, 'YYYY-MM-DD"T"hh24:mi:sstzh:tze') APPDATE,
FROM
mwo mw

error:
ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"
*Cause:    
*Action:

I'm not that familiar with formatting dates. How do I format this to add time zone (eastern standard time).

I tried

SELECT
    to_char(mw.appdate, 'YYYY-MM-DD"T"hh24:mi:sstzh:est')   APPDATE
    FROM
    mwo mw

and it returns this error:

ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"
*Cause:    
*Action:

I tried

mw.appdate, 'YYYY-MM-DD"T"hh24:mi:sstzh:est'   APPDATE

and it adds a column called APPDATE_1 which literally has this string in it: YYYY-MM-DD"T"hh24:mi:sstzh:est

UPDATE: I want to make the query return the re-formatted date. I can't re-insert into the db. I'm trying like is in the answer below, but it's still giving an error.

SELECT
   mw.account ACCT,
   mw.wono WO,
   to_char(mw.appdate, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS   APPDATE
         
FROM
   mwo mw

ERROR:

ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"
*Cause:    
*Action:

What is the data type of column `APPDATE`? Is it already given in eastern standard time? — Wernfried Domscheit, Sep 28 '21 at 12:50
What is "eastern standard time"? `SELECT TZNAME, TZABBREV, TZ_OFFSET(TZNAME) FROM V$TIMEZONE_NAMES WHERE TZABBREV = 'EST'` returns 68 rows. — Wernfried Domscheit, Sep 28 '21 at 12:54
APPDATE is a date data_type in the db. It doesn't have a time zone label. I tried mw.appdate, 'YYYY-MM-DD"T"hh24:mi:sstzh:est' APPDATE, for the APPDATE line in the query and it's adding an extra column called APPDATE_1, and literally returns this YYYY-MM-DD"T"hh24:mi:sstzh:est. — Michele, Sep 28 '21 at 13:03

score 0 · Answer 1 · answered Sep 28 '21 at 12:53

0

Assuming that your column is a TIMESTAMP WITH TIME ZONE data type and is already in Eastern Standard Time then you want the format model TZM (not TZE):

SELECT TO_CHAR(
          appdate,
          'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'
        ) AS APPDATE
FROM    mwo;

Then for the sample data:

CREATE TABLE mwo (appdate TIMESTAMP WITH TIME ZONE);

INSERT INTO mwo(appdate) VALUES (TIMESTAMP '2021-09-28 01:23:45.123456789 EST');

The output is:

APPDATE

2021-09-28T01:23:45-05:00

db<>fiddle here

answered Sep 28 '21 at 12:53

MT0

143,790
11
59
117

I have a long query I wanted to make the change and return the date there. How do I make it in my query and not in the db and reinsert? – Michele Sep 28 '21 at 13:10
It doesn't have time zone and anything but 00:00 for time in the table, like you do with your fiddle/insert into the table. – Michele Sep 28 '21 at 13:30

Wernfried Domscheit · Answer 2 · 2021-09-28T13:35:39.043

0

You need to cast the DATE into a TIMESTAMP, then you can attach a time zone and finally you can format the output:

SELECT
   to_char(
      FROM_TZ(CAST(mw.appdate AS TIMESTAMP), 'EST5EDT'), 
      'YYYY-MM-DD"T"hh24:mi:sstzh:tzm'
      ) AS APPDATE
FROM mw

edited Sep 28 '21 at 13:35

answered Sep 28 '21 at 13:15

Wernfried Domscheit

54,457
9
76
110

When I tried SELECT mw.account ACCOUNT, mw.wo WO, to_char( FROM_TZ_(CAST(mw.appdate AS TIMEZONE), 'ESTSEDT'), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' ) AS APPDATE FROM mwo mw, I get ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause: *Action: Error at Line: 7 Column: 38 – Michele Sep 28 '21 at 13:20
that select gives Typ=12 Len=7: w,c7,^C,^Z,^A,^A,^A (a long list of that) – Michele Sep 28 '21 at 13:32
This is from the table: APPDATE, DATE, Yes,, 12 ,, where COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, COLUMN_ID, COMMENTS is the headings – Michele Sep 28 '21 at 13:35
A typo, sorry. Must be `CAST(mw.appdate AS TIMESTAMP)` instead of `CAST(mw.appdate AS TIMEZONE)` – Wernfried Domscheit Sep 28 '21 at 13:36
I made that change to use timestamp instead of timezone and it gives this error: ORA-00904: "FROM_TZ_": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 8 Column: 10 – Michele Sep 28 '21 at 13:37
`FROM_TZ` instead of `FROM_TZ_` – Wernfried Domscheit Sep 28 '21 at 13:38
I tried select to_char( CAST(mw.appdate AS TIMESTAMP), 'ESTSEDT'), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' AS APPDATE and it gives this error: ORA-01821: date format not recognized 01821. 00000 - "date format not recognized" *Cause: *Action: – Michele Sep 28 '21 at 13:39
Removing the extra underscore still has an error. select to_char( FROM_TZ( CAST(mw.appdate AS TIMESTAMP), 'ESTSEDT'), 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM' ) AS APPDATE from mwo mw, has error: ORA-01882: timezone region not found 01882. 00000 - "timezone region not found" *Cause: The specified region name was not found. *Action: Contact Oracle Support Services. – Michele Sep 28 '21 at 13:41
1

`EST5EDT` not `ESTSEDT`, check valid time zone names in view `V$TIMEZONE_NAMES` – Wernfried Domscheit Sep 28 '21 at 13:53
It doesn't have an error with the EST5EDT change, but when it's showing the date on the screen, it looks like this: 2021-09-23T00:00:00-04:00. I'm not sure what the -04.00 part is. – Michele Sep 28 '21 at 14:16
`-04:00` is the time zone given as UTC offset. What do you expect? – Wernfried Domscheit Sep 28 '21 at 14:25

How convert time in query and add time zone - date format not recognized

2 Answers2

Linked