How to remove trailing 0s and offset from Oracle db-CodePudding

I'm working with an Oracle database (version 12c) containing a date column with the dates in the format YYY-MM-DD H23:Mi:SS.

All the dates are stored in EST.

I'm converting these dates to UTC so 2021-09-23 09:12:22 would appear as 2021-09-23 14:12:22.

I have the following query:

SELECT my_date,
       FROM_TZ(CAST(my_date AS TIMESTAMP), 'America/New_York') AT TIME ZONE 'UTC' AS my_date_utc
FROM my_table

Here is a sample of my results:

my_date               my_date_utc
-------------------   -------------------
2018-04-28 21:21:26   2018-04-29 01:21:26.000000  00:00
2018-12-18 07:42:25   2018-12-18 12:42:25.000000  00:00

How do I remove the ".000000 00:00" from the results? So the result would appear as:

my_date               my_date_utc
-------------------   -------------------
2018-04-28 21:21:26   2018-04-29 01:21:26
2018-12-18 07:42:25   2018-12-18 12:42:25

CodePudding user response：

To display a timestamp with time zone in whatever (valid) desired format, you need to use to_char. Something like this (not tested):

SELECT my_date,
       to_char(
         FROM_TZ(CAST(my_date AS TIMESTAMP), 'America/New_York') 
                 AT TIME ZONE 'UTC', 'yyyy-mm-dd hh24:mi:ss') AS my_date_utc
FROM my_table