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

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:

CodePudding user response：

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

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

db<>fiddle here

CodePudding user response：

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