1

I'm using this expression to convert a date at UTC to its US/Eastern time equivalent:

CAST((FROM_TZ(CAST(date_field AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Eastern') AS DATE) DATE_FIELD_ET

I was hoping to use a more generic expression to cast the value to the local time:

CAST((FROM_TZ(CAST(note_time AS TIMESTAMP),'+00:00') AT LOCAL TIME ZONE) AS DATE) DATE_FIELD_LOCAL

But this syntax doesn't work.

Is there a way to do this?

craig
  • 25,664
  • 27
  • 119
  • 205

1 Answers1

0

You could use DBTIMEZONE.

CAST((FROM_TZ(CAST(date_field AS TIMESTAMP),'+00:00') AT TIME ZONE DBTIMEZONE) AS DATE) DATE_FIELD_ET
Alex
  • 9,313
  • 1
  • 39
  • 44
  • I assume that this will automatically adjust for daylight savings. Am I correct? – craig Apr 10 '12 at 15:50
  • Are you asking: if the timezone the database is in respects daylight savings, will the code still work? (Just clarifying.) – Alex Apr 10 '12 at 15:52
  • Yes, that is what I am asking. – craig Apr 10 '12 at 16:37
  • `DBTIMEZONE` will return whatever timezone the database server is in. This generally won't change with daylight savings as this is considered a bad practice ([see here](http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices)). – Alex Apr 10 '12 at 19:48