2

Using AT TIME ZONE is there a way to get my UTC Time without the 00:00 AT The end without using a LEFT in my query.

im doing this: 

SELECT GETDATE() AT TIME ZONE 'EASTERN standard time' at time zone 'UTC'
Answer: 2018-03-05 15:08:00.930 +00:00

and trying to see if there is a better way other than doing 

SELECT LEFT(GETDATE() AT TIME ZONE 'EASTERN standard time' at time zone 'UTC',23)
Tony Montana
  • 39
  • 8
  • what will you get if you query `select getdate()` – Ven Mar 05 '18 at 15:12
  • I get my local eastern standard time which is 2018-03-05 10:13:23.453 but i'm trying to convert it to UTC – Tony Montana Mar 05 '18 at 15:14
  • This looks like a really complicated way of writing `SELECT SYSUTCDATETIME()`. – Jeroen Mostert Mar 05 '18 at 15:15
  • so the issue that i'm having is my table has an AuditTimestamp field using getdate() but i need to convert the date to UTC in order to interact with another database that has UTC time so i am trying to convert it @JeroenMostert – Tony Montana Mar 05 '18 at 15:21
  • 1
    That is intrinsically problematic, because some local date/time stamps will be ambiguous -- precisely when you go from summer to winter time, and an hour is "repeated". If you know the time you obtain this way is correct, though, you can simply get rid of the time zone info by `CONVERT`ing it to a `DATETIME2`. – Jeroen Mostert Mar 05 '18 at 15:28

2 Answers2

1

I would say declare it as variable and get datetime info from the datetimeoffset Convert to datetime will do the trick

    DECLARE @MyUtctime DATETIMEOFFSET

    SET @MyUtctime = (
            SELECT getdate() AT TIME ZONE 'EASTERN standard time' at TIME zone 'UTC'
            )

    SELECT CONVERT(DATETIME, @MyUtctime, 1)

    SELECT getdate()
Ven
  • 2,011
  • 1
  • 13
  • 27
1

The following code will output your desired results:

CONVERT(VARCHAR, CONVERT(DATETIME, <DateField> AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC'), 100) AS [LocalDateTime]

If your EST date was '2018-03-22 22:48:24.893' the output UTC date would be 'Mar 22 2018 2:48AM'.

Brian Smith
  • 1,467
  • 15
  • 31