Converting UTC to EST - Snowflake !! Remove Z and T from the date-CodePudding

I need to export this file to excel but it has those Z and T in the time stamp. How can i remove it? I did try to cast or use to_date but that didn't work. This gives me CallBgn or CallEnd as "2022-08-29T09:57:18.297Z". I want it to be "2022-08-29 09:57:18.297"

SELECT distinct      top 100  
                    b.ID as ID
                    ,convert_timezone('UTC','America/New_York',b.start_datetime_utc::timestamp) as CallBgn
                    ,convert_timezone('UTC','America/New_York',b.end_datetime_utc::timestamp)   as CallEnd
                    ,a.PNumber
From                pd_presentation.cus.cus_iss as a
left join           PD_PRESENTATION.CUS.CALL as b
on                  a.p_hk = b.p_hk
where               b.start_datetime_utc between '2022-08-28 00:00:00.000' AND '2022-09-17 23:59:59.997'
and                 a.p <> 'NOT GIVEN'

CodePudding user response：

It is just a matter of presentation, so:

ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

CodePudding user response：

Not certain what version of SQL you are using, but I would suggest substring manipulation to cut off the unneeded character. I would also suggest using the Excel functions rather than the SQL functions is more expedient if your data set is relatively small.

Mariadb convert_tz function does not append Z or T as you mention.

MariaDB [locks]> select now(),convert_tz(now(),' 00:00',' 06:00');
 --------------------- ------------------------------------- 
| now()               | convert_tz(now(),' 00:00',' 06:00') |
 --------------------- ------------------------------------- 
| 2022-09-22 16:17:09 | 2022-09-22 22:17:09                 |
 --------------------- ------------------------------------- 
1 row in set (0.00 sec)

The following chops the seconds off the current time using either substr or left functions:

MariaDB [locks]> select now(),substr(now(),1,16), left(now(),16);;
 --------------------- ------------------ ------------------ 
| now()               | substr(now(),1,16)   | left(now(),16)   |
 --------------------- ------------------ ------------------ 
| 2022-09-22 16:11:14 | 2022-09-22 16:11 | 2022-09-22 16:11 |
 --------------------- ------------------ ------------------ 
1 row in set (0.00 sec)