How to convert string to datetime with correct timezone?

najmead
Contributor

‎01-24-2023 08:33 PM

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?

Labels: