0

I have the following script giving me: 

  Select    ENDTIME_UTC AT TIME ZONE 'E. South America Standard Time' as TransactionDate_ESAST
            ,ENDTIME_UTC as TransactionDate
    From   Table_CYC
  --Results: 2019-11-09 21:02:28.000 -03:00, 2019-11-09 21:02:28.000

However, I would want it either -3 (as an integer for just the offset portion) or 2019-11-09 18:02:28.000, where the hour is subtracted by three.

What would be the best way to go about this? Thanks, Yolanda

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Yola Z
  • 39
  • 1
  • 3

1 Answers1

1

As currently written you are asserting that the existing value is in Brasilia time and not performing any time zone conversion. Instead, you need to assert that the existing value is in UTC and then convert to Brasilia time.

This is done by calling AT TIME ZONE twice.

SELECT ENDTIME_UTC AT TIME ZONE 'UTC' AT TIME ZONE 'E. South America Standard Time'
  • The first AT TIME ZONE creates a datetimeoffset from your input datetime asserting that it should be applied with the UTC time zone.
  • The second AT TIME ZONE then converts from that datetimeoffset to another datetimeoffset with the given time zone.

Note that this is only necessary because your original field is of type datetime (or datetime2, etc.). If instead your field was a datetimeoffset, then your original code would work fine.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • As far as getting the offset by itself, after performing the above you can use the technique [described in this answer](https://stackoverflow.com/a/15056465/634824) to extract the offset from the result. – Matt Johnson-Pint Nov 20 '19 at 23:15