If it's 5:00 P.M in the CST zone, then it is 6:00 P.M in the EST zone. The Eastern zone is one hour ahead of the Central zone. When converting between these two zones, it's best to remember that these two time zones are adjacent to one another, meaning they are exactly an hour off from each other. Also, tables are available for easy reference to various conversions as you see fit. In this case we're talking about converting Central Standard Time to Eastern Standard Time.īeyond this convenient tool, we also have live clocks for both of these areas that adjust for factors like Daylight Saving Time. If the weekday is less than or equal to the calendar day it's Winter daylight but we need to account for the hours leading up to 2am. WHEN DATEPART ( WEEKDAY, UTC.Field ) = 1ĪND UTC.Field DATEPART ( DAY, UTC.Field )ĪND DATEPART ( WEEKDAY, UTC.Field ) <= DATEPART ( DAY, UTC.Field ) However, if UTC is before 9am on that Sunday, then it's before 2am Mountain which means it's still Winter daylight time. WHEN MONTH ( UTC.Field ) > 3 AND MONTH ( UTC.Field ) = 14 When it's between March and November, it is summer time which is -6 from UTC Adjust a UTC value, in the example the UTC field is identified as UTC.Field, to account for daylight savings time when converting out of UTC to Mountain time. For EST, you would just change all the -6 to -4 and change all the -7 to -5.
#CST TIME TO EST CODE#
The code below converts a UTC field to Mountain Time accounting for daylight savings. If you want to do this without calling a function, you can do it with a CASE statement as well. SELECT = CONVERT(DATETIME,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') SELECT = GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' Lastly, we'll wrap the entire thing in CONVERT() to get it back to a datetime, dropping the unneeded +/- hours portion during the process. Next, we'll call AT TIMEZONE again to cut it over to EST. Implicitly, this is also changing it's datatype from a datetime to datetimeoffset. The query above calls GETDATE() and sets it's timezone as UTC using AT TIMEZONE. There may be some functions and features that are new here, so an explanation is warranted. Above, we are using the AT TIME ZONE features, described in more detail here. SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') For example, it can be called from reports or used on databases that are read-only. As of SQL Server 2016, converting between timezones can be done with a single line of native sql. No new stored procedure, or scalar/user defined functions are needed. There are a lot of answers here that are unnecessarily complex, or that don't account for daylight savings time. This query will work on any Azure SQL Server. SELECT CONVERT(DATETIME,GETDATE() AT TIME ZONE (SELECT CURRENT_TIMEZONE_ID()) AT TIME ZONE 'Eastern Standard Time') Adding this function into the ORIGINAL ANSWER below yields a single query will work globally on all Azure SQL Servers. SELECT CURRENT_TIMEZONE_ID() will return your server's timezone. The Azure SQL team has released a new function which makes this even easier.