Convert UTC to EST in Query [MS SQL]-CodePudding

Long time reader first time poster :)

I am making a query that has a date formatted like this 2022-03-01T04:59:59.000 0000 since this is in UTC I need to get it into EST. I am having issues converting this. I have tried a few things I found online. The closest I have gotten is this.

SELECT CONVERT(datetime, 
             SWITCHOFFSET(CONVERT(datetimeoffset, 
                                   convert(varchar, [UTC Date Field], 1)), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
AS ColumnInLocalTime
from [Table Name] a (nolock)

But this is giving me an Conversion failed when converting date and/or time from character string. error. I am using MS SQL Server and I would consider my self competent in SQL. I know the data in the field I am using is stored as varchar. Any help would be appreciated.

CodePudding user response：

Your value is not in a ISO8601 format that SQL Server recognizes, it's missing a : in the time zone part.

Let's just say you wouldn't have had this issue if your data would have been stored as datetimeoffset in the first place.

You can patch it up using STUFF, then just CONVERT with style 126

SELECT
  SWITCHOFFSET(
    CONVERT(datetimeoffset,
      STUFF('2022-03-01T04:59:59.000 0000', 27, 0, ':'),
      126),
    DATENAME(TzOffset, SYSDATETIMEOFFSET())
  )

db<>fiddle