Date conversion to 12 hr in Snowflake

1
votes

I have a table

SELECT
    DATE_CREATED_AT AS Timestamp,
    TO_TIMESTAMP(CONVERT_TIMEZONE('America/New_York', Timestamp::timestampntz)) AS EST
FROM
    "MyTable"

This query returns data in the following format:

EST
------------------------
2020-03-05 18:45:04.000

But I want EST to return data in the following format

EST
--------------------
2020-03-05 6:45:00

How do I do that?

Thanks in advance

2
sqldatesnowflake-cloud-data-platform
use this dd/mm/yyyy hh12:mi:ss am to convert into 12 hrs format. – zealous
to @zealous a "timestamp" has no format, it is just a timestamp, and if you are wanting it is a presentation format "a string" you should covert it to said string in the form that you want. Which should ether be done outside the DB in the presentation layer, or if you need to do that prior, except you no long have/need a timestamp and convert it to the projection you wish for.. – Simeon Pilgrim
In addition to the wise words by @SimeonPilgrim - using ambiguous output is never a great idea 6:45:00 could be either AM or PM – Paul Maxwell

2 Answers

0
votes

Here is how you can convert timestamp to string and specify the format you want without AM/PM information.

select to_varchar(CONVERT_TIMEZONE('America/New_York',current_timestamp()), 'YYYY-MM-DD HH12:MI:SS')
0
votes
select to_varchar(convert_timezone('America/Los_Angeles',
                                   '2021-07-05 18:37:05.000'::timestamp_ntz),
                                   'YYYY-MM-DD HH12:MI:SS AM');

input = 2021-07-05 18:37:05.000 and Result = 2021-07-05 06:37:05 PM

select to_varchar(convert_timezone('America/Los_Angeles', 
                                   '2021-07-05 11:37:05.000'::timestamp_ntz), 
                                   'YYYY-MM-DD HH12:MI:SS AM');

input = 2021-07-05 11:37:05.000 and Result = 2021-07-05 11:37:05 AM