How to convert string to datetime with correct timezone?

[najmead]

I have a field stored as a string in the format "12/30/2022 10:30:00 AM"

• If I use the function TO_DATE, I only get the date part... I want the full date and time.
• If I use the function TO_TIMESTAMP, I get the date and time, but it's assumed to be UTC, which isn't correct (it's actually the local timezone where the data was originally hosted)
• I can potentially use MAKE_TIMESTAMP, but I have to do a lot of string manipulation to get all the components

What is the easiest way to convert the string into a timestamp, with the timezone of my choosing?

[daniel_sahal]

@Nicholas Mead​ 

You can use TO_TIMESTAMP() function with format parameter.

Ex.

TO_TIMESTAMP("12/30/2022 10:30:00 AM", "MM/dd/yyyy HH:mm:ss a") AS tsDate

[najmead]

I don't seem to be getting the same result... note no timezone info in my results;

Is there a config setting I'm missing or something?

[najmead]

Just to answer my own question, in case anyone else googles for this...

FirstIy, don't know why timezone isn't displaying in my query results using a SQL Warehouse, but it works ok using a compute cluster, so I'm guessing it is a display issue rather than a data issue.

Secondly, from what I can tell, using the TO_TIMESTAMP function will apply the timezone for your session (which might not be the timezone you want). I didn't realise this could be solved pretty easily by simply pasting your timezone to the end of the date string, like this;

TO_TIMESTAMP(MyDateTimeString || ' Timezone', 'M/d/y h:m:s a z)

For example;

TO_TIMESTAMP(MyDateTimeString || ' America/Los_Angeles', 'M/d/y h:m:s a z)

[Manoj12421]

Use to_utc_timestamp(expr,timezone)​

[SRK]

You can use this to get the time zone of your choosing:

[David_Billa]

TO_DATE("12/30/2022 10:30:00 AM", "MM/dd/yyyy HH:mm:ss a") AS tsDate

Is this correct if we need only date?

[Rajeev_Basu]

use from_utc_timestamp(to_timestam("<string>", <format>),<timezone>)