0

I am using MS SQL Server 2008 and have following statement to display timestamp

select  CAST (DATEADD(SECOND, dbo.V_ALERTS.TIME_STAMP /1000 + 8*60*60, '19700101') AS VARCHAR(50)) as "Time_in_UTC",*
from dbo.V_ALERTS where FILEPATH like '%usb%' 
ORDER BY dbo.V_ALERTS.TIME_STAMP DESC

Timestamp in Database is in UTC, how to convert to Eastern Standard Time (EST)?

Glowie
  • 2,271
  • 21
  • 60
  • 104
  • 3
    Two things. One, you probably mean America/New_York, rather than EST (EST is ONLY half of the year..the other half of the year is EDT). Second, this is a duplicate of http://stackoverflow.com/questions/6064674/sql-server-2008-how-to-convert-gmtutc-datetime-to-local-datetime?rq=1 – dodexahedron Aug 08 '14 at 13:17
  • 1
    It depends on whether DST is being observed on the date, so there isn't a very simple way to do this in T-SQL. See this series for some ideas: http://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/ http://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/ http://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/ Otherwise, handle the conversion at the presentation layer. – Aaron Bertrand Aug 08 '14 at 13:23
  • That's why we now have the `DATETIMEOFFSET` data type, which correctly represents an exact moment in time, rather than an offset-dependent relative time value. I suggest everyone please try to use it as often as possible instead of DATETIME, since it's usually what you actually mean. – dodexahedron Aug 08 '14 at 13:24
  • @dodexahedron Except DATETIMEOFFSET is not DST-aware. – Aaron Bertrand Aug 08 '14 at 13:24
  • It implicitly is because it includes the offset of the time, as recorded. The system handles conversions for you implicitly, ie if you ask for a time in UTC, it'll make the 4 hour adjustment for you. – dodexahedron Aug 08 '14 at 13:26
  • @dodexahedron But the OP already has a boatload of data that wasn't recorded that way, and if he records UTC (with +00:00), how is he going to get the right time on output? SwitchOffset() requires a string like '-05:00' - pretty complicated expression you'll have to write to identify whether you should -4 or -5. – Aaron Bertrand Aug 08 '14 at 13:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/58963/discussion-between-dodexahedron-and-aaron-bertrand). – dodexahedron Aug 08 '14 at 13:31

0 Answers0