Convert Datetime column from UTC to local time in select statement

Question

I m doing a few SQL select queries and would like to convert my UTC datetime column into local time to be displayed as local time in my query results  Note  I am NOT looking to do this conversion via code but rather when I am doing manual and random SQL queries against my databases

User · Answer

You have to reformat the string as well as converting to the correct time   In this case I needed Zulu time   Declare  Date datetime  Declare  DateString varchar 50   set  Date   GETDATE     declare  ZuluTime datetime   Declare  DateFrom varchar  50   Declare  DateTo varchar  50   set  ZuluTime   DATEADD second  DATEDIFF second  GETDATE    GETUTCDATE      Date   set  DateString    FORMAT  ZuluTime   yyyy-MM-ddThh mm ssZ    en-US      select  DateString

User · Answer

It s simple  Try this for Azure SQL Server   SELECT YourDateTimeColumn AT TIME ZONE  Eastern Standard Time  FROM YourTable   For Local SQL Server    SELECT CONVERT datetime2  SWITCHOFFSET CONVERT datetimeoffset  gETDATE     DATENAME TzOffset  gETDATE   AT TIME ZONE  Eastern Standard Time     FROM YourTable

User · Answer

I have code to perform UTC to Local and Local to UTC times which allows conversion using code like this  DECLARE  usersTimezone VARCHAR 32   Europe London  DECLARE  utcDT DATETIME GetUTCDate   DECLARE  userDT DATETIME  dbo   funcUTCtoLocal   utcDT   usersTimezone    and   DECLARE  usersTimezone VARCHAR 32   Europe London  DECLARE  userDT DATETIME GetDate   DECLARE  utcDT DATETIME  dbo   funcLocaltoUTC   userDT   usersTimezone    The functions can support all or a subset of timezones in the IANA TZDB as provided by NodaTime - see the full list at https   nodatime org TimeZones  Be aware that my use case means I only need a  current  window  allowing the conversion of times within the range of about   - 5 years from now   This means that the method I ve used probably isn t suitable for you if you need a very wide period of time  due to the way it generates code for each timezone interval in a given date range   The project is on GitHub  https   github com elliveny SQLServerTimeConversion  This generates SQL function code as per this example

User · Answer

If you need a conversion other than your server s location  here is a function that allows you to pass a standard offset and accounts for US Daylight Savings Times   --                                               -- Author       Ron Smith -- Create date  2013-10-23 -- Description  Converts UTC to DST --              based on passed Standard offset --                                               CREATE FUNCTION  dbo   fn UTC to DST         UTC datetime       StandardOffset int   RETURNS datetime AS BEGIN      declare           DST datetime           SSM datetime  -- Second Sunday in March          FSN datetime  -- First Sunday in November      -- get DST Range     set  SSM   datename year  UTC     0314       set  SSM   dateadd hour 2 dateadd day datepart dw  SSM  -1 1  SSM       set  FSN   datename year  UTC     1107      set  FSN   dateadd second -1 dateadd hour 2 dateadd day datepart dw  FSN  -1 1  FSN         -- add an hour to  StandardOffset if  UTC is in DST range     if  UTC between  SSM and  FSN         set  StandardOffset    StandardOffset   1      -- convert to DST     set  DST   dateadd hour  StandardOffset  UTC       -- return converted datetime     return  DST  END  GO

User · Answer

You can do this as follows on SQL Server 2008 or greater   SELECT CONVERT datetime                  SWITCHOFFSET CONVERT datetimeoffset                                       MyTable UtcColumn                                DATENAME TzOffset  SYSDATETIMEOFFSET              AS ColumnInLocalTime FROM MyTable   You can also do the less verbose   SELECT DATEADD mi  DATEDIFF mi  GETUTCDATE    GETDATE     MyTable UtcColumn          AS ColumnInLocalTime FROM MyTable   Whatever you do  do not use - to subtract dates  because the operation is not atomic  and you will on occasion get indeterminate results due to race conditions between the system datetime and the local datetime being checked at different times  i e   non-atomically    Please note that this answer does not take DST into account  If you want to include a DST adjustment  please also see the following SO question   How to create Daylight Savings time Start and End function in SQL Server

User · Answer

I didn t find any of these example helpful in getting a datetime stored as UTC to a datetime in a specified timezone  NOT the timezone of the server because Azure SQL databases run as UTC   This is how I handled it  It s not elegant but it s simple and gives you the right answer without maintaining other tables   select CONVERT datetime  SWITCHOFFSET dateTimeField  DATEPART TZOFFSET   dateTimeField AT TIME ZONE  Eastern Standard Time

User · Answer

Here s a version that accounts for daylight savings  UTC offset  and is not locked into a particular year   --------------------------------------------------------------------------------------------------- --Name      udfToLocalTime sql --Purpose   To convert UTC to local US time accounting for DST --Author    Patrick Slesicki --Date      3 25 2014 --Notes     Works on SQL Server 2008R2 and later  maybe SQL Server 2008 as well  --          Good only for US States observing the Energy Policy Act of 2005  --          Function doesn t apply for years prior to 2007  --          Function assumes that the 1st day of the week is Sunday  --Tests          --          SELECT dbo udfToLocalTime  2014-03-09 9 00   DEFAULT  --          SELECT dbo udfToLocalTime  2014-03-09 10 00   DEFAULT  --          SELECT dbo udfToLocalTime  2014-11-02 8 00   DEFAULT  --          SELECT dbo udfToLocalTime  2014-11-02 9 00   DEFAULT  --------------------------------------------------------------------------------------------------- ALTER FUNCTION udfToLocalTime            UtcDateTime    AS DATETIME       UtcOffset     AS INT   -8 --PST       RETURNS DATETIME AS  BEGIN     DECLARE           PstDateTime    AS DATETIME           Year          AS CHAR 4            DstStart      AS DATETIME           DstEnd        AS DATETIME           Mar1          AS DATETIME           Nov1          AS DATETIME           MarTime       AS TIME           NovTime       AS TIME           Mar1Day       AS INT           Nov1Day       AS INT           MarDiff       AS INT           NovDiff       AS INT      SELECT          Year         YEAR  UtcDateTime            MarTime     CONVERT TIME  DATEADD HOUR  - UtcOffset   1900-01-01 02 00              NovTime     CONVERT TIME  DATEADD HOUR  - UtcOffset - 1   1900-01-01 02 00              Mar1        CONVERT CHAR 16    Year    -03-01     CONVERT CHAR 5    MarTime   126            Nov1        CONVERT CHAR 16    Year    -11-01     CONVERT CHAR 5    NovTime   126            Mar1Day     DATEPART WEEKDAY   Mar1            Nov1Day     DATEPART WEEKDAY   Nov1       --Get number of days between Mar 1 and DST start date     IF  Mar1Day   1 SET  MarDiff   7     ELSE SET  MarDiff   15 -  Mar1Day      --Get number of days between Nov 1 and DST end date     IF  Nov1Day   1 SET  NovDiff   0     ELSE SET  NovDiff   8 -  Nov1Day      --Get DST start and end dates     SELECT           DstStart     DATEADD DAY   MarDiff   Mar1            DstEnd      DATEADD DAY   NovDiff   Nov1       --Change UTC offset if  UtcDateTime is in DST Range     IF  UtcDateTime  gt    DstStart AND  UtcDateTime  lt   DstEnd SET  UtcOffset    UtcOffset   1      --Get Conversion     SET  PstDateTime   DATEADD HOUR   UtcOffset   UtcDateTime      RETURN  PstDateTime END GO

User · Answer

For anyone still trying to solve this issue  here s a proof of concept that works in SQL Server 2017  declare      StartDate date    2020-01-01    with cte utc as       select           1 as i          CONVERT datetime   StartDate  AS UTC          datepart weekday  CONVERT datetime   StartDate   as Weekday          datepart month  CONVERT datetime   StartDate   as  Month           datepart YEAR  CONVERT datetime   StartDate   as  Year               union all      Select          i   1          dateadd d  1  utc           datepart weekday  CONVERT datetime  dateadd d  1  utc    as Weekday          datepart month  CONVERT datetime  dateadd d  1  utc    as  Month           datepart YEAR  CONVERT datetime  dateadd d  1  utc    as  Year      from             cte utc     where          i   1   lt  32767     cte utc dates as        select                     DENSE RANK  OVER PARTITION BY  Year    Month    Weekday  ORDER BY Utc  WeekDayIndex     from         cte utc     cte hours as       select 0 as  Hour      union all     select  Hour    1 from cte hours where  Hour   lt  23    select     d         DATEADD hour  h Hour  d UTC  AS UtcTime      CONVERT datetime  DATEADD hour  h Hour  d UTC  AT TIME ZONE  UTC  AT TIME ZONE  Central Standard Time   CST      CONVERT datetime  DATEADD hour  h Hour  d UTC  AT TIME ZONE  UTC  AT TIME ZONE  Eastern Standard Time   EST from     cte utc dates d  cte hours h where       Month    3 and  Weekday    1 and WeekDayIndex   2  -- dst start     or        Month    11 and  Weekday    1 and WeekDayIndex   1  -- dst end order by     utc OPTION  MAXRECURSION 32767   GO

User · Answer

This should be able to get server time with DST  declare  dt datetime set  dt   getutcdate   -- GMT equivalent   sysdatetimeoffset takes DST into account  select  InputTime     dt           LocalTime2    dateadd mi  datediff mi  sysdatetimeoffset   getdate      dt

User · Answer

Ron s answer contains an error  It uses 2 00 AM local time where the UTC equivalent is required  I don t have enough reputation points to comment on Ron s answer so a corrected version appears below   --                                               -- Author       Ron Smith -- Create date  2013-10-23 -- Description  Converts UTC to DST --              based on passed Standard offset --                                               CREATE FUNCTION  dbo   fn UTC to DST         UTC datetime       StandardOffset int   RETURNS datetime AS BEGIN  declare       DST datetime       SSM datetime  -- Second Sunday in March      FSN datetime  -- First Sunday in November -- get DST Range set  SSM   datename year  UTC     0314   set  SSM   dateadd hour 2 -  StandardOffset dateadd day datepart dw  SSM  -1 1  SSM   set  FSN   datename year  UTC     1107  set  FSN   dateadd second -1 dateadd hour 2 -   StandardOffset   1  dateadd day datepart dw  FSN  -1 1  FSN     -- add an hour to  StandardOffset if  UTC is in DST range if  UTC between  SSM and  FSN     set  StandardOffset    StandardOffset   1  -- convert to DST set  DST   dateadd hour  StandardOffset  UTC   -- return converted datetime return  DST  END

User · Answer

Using new SQL Server 2016 opportunities   CREATE FUNCTION ToLocalTime  dtUtc datetime   timezoneId nvarchar 256   RETURNS datetime AS BEGIN  return  dtUtc AT TIME ZONE  UTC  AT TIME ZONE  timezoneId     -- second way  faster  return SWITCHOFFSET  dtUtc   DATENAME tz   dtUtc AT TIME ZONE  timezoneId           -- third way  declare  dtLocal datetimeoffset set  dtLocal    dtUtc AT TIME ZONE  timezoneId return dateadd minute  DATEPART  TZoffset   dtLocal    dtUtc       END GO   But clr procedure works in 5 times faster   -   Pay attention that Offset for one TimeZone can change to winter or summer time  For example  select cast  2017-02-08 09 00 00 000  as datetime  AT TIME ZONE  Eastern Standard Time  select cast  2017-08-08 09 00 00 000  as datetime  AT TIME ZONE  Eastern Standard Time    results   2017-02-08 09 00 00 000 -05 00 2017-08-08 09 00 00 000 -04 00   You can t just add constant offset

User · Answer

This function will convert a UTC time to EST time with DST adjustment  You can change your designed time zone name in this function  or get it from registry  Create Function fnConvertUTCTimetoESTTime       UTCTime as datetime   returns datetime as begin     return convert datetime  convert varchar 23    UTCTime AT TIME ZONE  UTC  AT TIME ZONE  Eastern Standard Time   121   121  end go  select dbo fnConvertUTCTimetoESTTime   2020-3-8 5 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-3-8 6 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-3-8 7 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-3-8 8 00 00 000    --returns 0 00am  1 00am  3 00am  4 00am  select dbo fnConvertUTCTimetoESTTime   2020-11-1 4 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-11-1 5 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-11-1 6 00 00 000         dbo fnConvertUTCTimetoESTTime   2020-11-1 7 00 00 000    --returns 0 00am  1 00am  1 00am  2 00am  Note you can not just return  quot  UTCTime AT TIME ZONE  UTC  AT TIME ZONE  Eastern Standard Time  quot  as the result because this result is actually a UTC time in EST format  when you compare this  quot fake quot  EST time or include it in an order clause it will be converted back to a UTC time

User · Answer

I ve found that this function is faster than other solutions using a separate table or loops  It s just a basic case statement  Given that all months between April and October have a -4-hour offset  Eastern Time  we just need to add a few more case lines for the fringe days  Otherwise  the offset is -5 hours   This is specific to a conversion from UTC to Eastern time  but additional time zone functions can be added as needed   USE  YourDatabaseName  GO          Object   UserDefinedFunction  dbo   ConvertUTCtoEastern     Script Date  11 2 2016 5 21 52 PM         SET ANSI NULLS ON GO  SET QUOTED IDENTIFIER ON GO   CREATE FUNCTION  dbo   ConvertUTCtoEastern     dtStartDate DATETIME   RETURNS DATETIME AS BEGIN DECLARE  Working DATETIME DECLARE  Returned DATETIME  SET  Working    dtStartDate SET  Working    case when month  Working  between 4 and 10 then dateadd HH -4  Working        when  Working between  2017-03-12  and  2017-11-05  then dateadd HH -4  Working        when  Working between  2016-03-13  and  2016-11-06  then dateadd HH -4  Working        when  Working between  2015-03-08  and  2015-11-01  then dateadd HH -4  Working        when  Working between  2014-03-09  and  2014-11-02  then dateadd HH -4  Working        when  Working between  2013-03-10  and  2013-11-03  then dateadd HH -4  Working        when  Working between  2012-03-11  and  2012-11-04  then dateadd HH -4  Working   else dateadd HH -5  Working  end  SET  Returned    Working  RETURN  Returned  END   GO

User · Answer

As a warning - if you re going to use the following  note the milliseconds instead of minutes        SELECT DATEADD ms  DATEDIFF ms  GETUTCDATE    GETDATE     MyTable UtcColumn       AS ColumnInLocalTime     FROM MyTable   Keep in mind that the DATEDIFF part will not always return the same number  So don t use it to compare DateTimes down to milliseconds

User · Answer

Here s a simpler one that takes dst in to account  CREATE FUNCTION  dbo   UtcToLocal          p utcDatetime DATETIME    RETURNS DATETIME AS BEGIN     RETURN DATEADD MINUTE  DATEDIFF MINUTE  GETUTCDATE     p utcDatetime   GETDATE    END

User · Answer

The UNIX timestamp is merely the number of seconds between a particular date and the Unix Epoch    SELECT DATEDIFF SECOND  d  1970-01-01   GETDATE       This Will Return the UNIX timestamp In SQL server  you can create a function for local date time to Unix UTC conversion using Country Offset  Function to Unix Time Stamp In SQL server

User · Answer

For Azure SQL and   Version     SQL Server 2016 users  Below is a simple function using AT TIME ZONE   CREATE FUNCTION  dbo   Global Convert UTCTimeTo LocalTime        LocalTimeZone        VARCHAR 50       UTCDateTime          DATETIME   RETURNS DATETIME AS BEGIN    DECLARE  ConvertedDateTime DATETIME      SELECT  ConvertedDateTime     UTCDateTime  AT  TIME  ZONE  UTC  AT TIME ZONE   LocalTimeZone    RETURN  ConvertedDateTime  END GO   For types of values that  LocalTimeZone can take  please go to this link or Go to KEY LOCAL MACHINE SOFTWARE Microsoft Windows NT CurrentVersion Time Zones

User · Answer

None of these worked for me but this below worked 100    Hope this can help others trying to convert it like I was   CREATE FUNCTION  dbo   fn UTC to EST         UTC datetime       StandardOffset int   RETURNS datetime AS BEGIN  declare       DST datetime       SSM datetime  -- Second Sunday in March      FSN datetime  -- First Sunday in November -- get DST Range set  SSM   DATEADD dd 7    6- DATEDIFF dd 0 DATEADD mm  YEAR GETDATE   -1900    12   2 0   7   DATEADD mm  YEAR GETDATE   -1900    12   2 0    02 00 00   set  FSN   DATEADD dd   6- DATEDIFF dd 0 DATEADD mm  YEAR GETDATE   -1900    12   10 0   7   DATEADD mm  YEAR GETDATE   -1900    12   10 0     02 00 00   -- add an hour to  StandardOffset if  UTC is in DST range if  UTC between  SSM and  FSN     set  StandardOffset    StandardOffset   1  -- convert to DST set  DST   dateadd hour  StandardOffset  UTC   -- return converted datetime return  DST  END

User · Answer

I found the one off function way to be too slow when there is a lot of data   So I did it through joining to a table function that would allow for a calculation of the hour diff  It is basically datetime segments with the hour offset  A year would be 4 rows   So the table function  dbo fn getTimeZoneOffsets  3 1 2007 7 00am    11 5 2007 9 00am    EPT     would return this table   startTime          endTime   offset  isHr2 3 1 07 7 00     3 11 07 6 59    -5    0 3 11 07 7 00    11 4 07 6 59    -4    0 11 4 07 7 00    11 4 07 7 59    -5    1 11 4 07 8 00    11 5 07 9 00    -5    0   It does account for daylight savings  A sample of how it is uses is below and the full blog post is here   select mt startTime as startUTC       dateadd hh  tzStart offset  mt startTime  as startLocal       tzStart isHr2 from MyTable mt  inner join dbo fn getTimeZoneOffsets  startViewUTC   endViewUTC   timeZone   tzStart on mt startTime between tzStart startTime and tzStart endTime

User · Answer

Best way for Oracle   With hardcoded datetime     SELECT TO CHAR CAST  FROM TZ CAST TO DATE  2018-10-27 21 00    YYYY-MM-DD HH24 MI   AS TIMESTAMP    UTC   AT  TIME ZONE  EET   AS DATE    YYYY-MM-DD HH24 MI   UTC TO EET FROM DUAL   Result  2018-10-28 00 00  With column and table names   SELECT TO CHAR CAST  FROM TZ CAST COLUMN NAME AS TIMESTAMP    UTC   AT  TIME ZONE  EET   AS DATE    YYYY-MM-DD HH24 MI   UTC TO EET FROM TABLE NAME

User · Answer

-- get indian standard time from utc  CREATE FUNCTION dbo getISTTime    UTCDate datetime   RETURNS datetime AS BEGIN      RETURN dateadd minute 330  UTCDate   END GO

User · Answer

There is no simple way to do this in a correct AND generic way   First of all it must be understood that the offset depends on the date in question  the Time Zone AND DST   GetDate  -GetUTCDate only gives you the offset today at the server s TZ  which is not relevant   I have seen only two working solution and I have search a lot   1  A custom SQL function with a a couple of tables of base data such as Time Zones and DST rules per TZ  Working but not very elegant  I can t post it since I don t own the code   EDIT  Here is an example of this method https   gist github com drumsta 16b79cee6bc195cd89c8  2  Add a  net assembly to the db   Net can do this very easily  This is working very well but the downside is that you need to configure several parameters on server level and the config is easily broken e g  if you restore the database  I use this method but I cant post it since I don t own the code

User · Answer

declare  mydate2 datetime  set  mydate2 Getdate    select  mydate2 as mydate   dateadd minute  datediff minute getdate    mydate2  getutcdate

User · Answer

In postgres this works very nicely  Tell the server the time at which the time is saved   utc   and then ask it to convert to a specific timezone  in this case  Brazil East  quiz step progresses created at  at time zone  utc  at time zone  Brazil East   Get a complete list of timezones with the following select  select   from pg timezone names   See details here  https   popsql com learn-sql postgresql how-to-convert-utc-to-local-time-zone-in-postgresql

User · Answer

This can be done without a function   Code below will convert a UTC time to Mountain time accounting for daylight savings   Adjust all the -6 and -7 numbers to your timezone accordingly  i e  for EST you would adjust to -4 and -5 respectively   --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  CASE     --When it s between March and November  it is summer time which is -6 from UTC     WHEN MONTH   UTC Field    gt  3 AND MONTH   UTC Field    lt  11          THEN DATEADD   HOUR   -6   UTC Field       --When its March and the day is greater than the 14  you know it s summer  -6      WHEN MONTH   UTC Field     3         AND DATEPART   DAY   UTC Field    gt   14          THEN             --However  if UTC is before 9am on that Sunday  then it s before 2am Mountain which means it s still Winter daylight time              CASE                  WHEN DATEPART   WEEKDAY   UTC Field     1                      AND UTC Field  lt   9 00                      --Before 2am mountain time so it s winter  -7 hours for Winter daylight time                     THEN DATEADD   HOUR   -7   UTC Field                   --Otherwise -6 because it ll be after 2am making it Summer daylight time                 ELSE DATEADD   HOUR   -6   UTC Field               END     WHEN MONTH   UTC Field     3         AND   DATEPART   WEEKDAY   UTC Field     7    lt   DATEPART   day   UTC Field            THEN              --According to the date  it s moved onto Summer daylight  but we need to account for the hours leading up to 2am if it s Sunday             CASE                  WHEN DATEPART   WEEKDAY   UTC Field     1                      AND UTC Field  lt   9 00                      --Before 9am UTC is before 2am Mountain so it s winter Daylight  -7 hours                     THEN DATEADD   HOUR   -7   UTC Field                   --Otherwise  it s summer daylight  -6 hours                 ELSE DATEADD   HOUR   -6   UTC Field               END     --When it s November and the weekday is greater than the calendar date  it s still Summer so -6 from the time     WHEN MONTH   UTC Field     11         AND DATEPART   WEEKDAY   UTC Field    gt  DATEPART   DAY   UTC Field            THEN DATEADD   HOUR   -6   UTC Field       WHEN MONTH   UTC Field     11         AND DATEPART   WEEKDAY   UTC Field    lt   DATEPART   DAY   UTC Field                --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              CASE                  WHEN DATEPART   WEEKDAY   UTC Field     1                      AND UTC Field  lt   8 00                      --If it s before 8am UTC and it s Sunday in the logic outlined  then it s still Summer daylight  -6 hours                     THEN DATEADD   HOUR   -6   UTC Field                   --Otherwise  adjust for Winter daylight at -7                 ELSE DATEADD   HOUR   -7   UTC Field               END     --If the date doesn t fall into any of the above logic  it s Winter daylight  -7     ELSE         DATEADD   HOUR   -7   UTC Field   END

User · Answer

If your local date time is say Eastern Standard Time and you want to convert from UTC to that  then in Azure SQL and SQL Server 2016 and above  you can do   SELECT YourUtcColumn AT TIME ZONE  UTC  AT TIME ZONE  Eastern Standard Time  AS        LocalTime FROM   YourTable   The full list of timezone names can be found with   SELECT   FROM sys time zone info    And yes  the timezones are badly named - even though it is Eastern Standard Time  daylight savings is taken into account

User · Answer

First function  configured for italian time zone   1   2   switch dates  last sunday of march and october  return the difference between the current time zone and the datetime as parameter    Returns  current timezone  lt  parameter timezone    gt   1 current timezone  gt  parameter timezone    gt  -1 else 0   The code is   CREATE FUNCTION  dbo   UF ADJUST OFFSET         dt utc datetime2 7    RETURNS INT AS BEGIN   declare  month int           year int           current offset int           offset since int           offset int           yearmonth varchar 8            changeoffsetdate datetime2 7   declare  lastweek table giorno datetime2 7    select  current offset   DATEDIFF hh  GETUTCDATE    GETDATE     select  month   datepart month   dt utc   if  month  lt  3 or  month  gt  10 Begin Set  offset since   1 Goto JMP End  if  month  gt  3 and  month  lt  10 Begin Set  offset since   2 Goto JMP End  --If i m here is march or october select  year   datepart yyyy   dt utc   if  month   3 Begin  Set  yearmonth   cast  year as varchar     -03-   Insert Into  lastweek Values  yearmonth    31 03 00 00 000000     yearmonth    30 03 00 00 000000     yearmonth    29 03 00 00 000000     yearmonth    28 03 00 00 000000                               yearmonth    27 03 00 00 000000     yearmonth    26 03 00 00 000000     yearmonth    25 03 00 00 000000    --Last week of march Select  changeoffsetdate   giorno From  lastweek Where  datepart weekday  giorno    1      if  dt utc  lt   changeoffsetdate      Begin          Set  offset since   1      End Else Begin         Set  offset since   2     End End  if  month   10 Begin  Set  yearmonth   cast  year as varchar     -10-   Insert Into  lastweek Values  yearmonth    31 03 00 00 000000     yearmonth    30 03 00 00 000000     yearmonth    29 03 00 00 000000     yearmonth    28 03 00 00 000000                               yearmonth    27 03 00 00 000000     yearmonth    26 03 00 00 000000     yearmonth    25 03 00 00 000000    --Last week of october Select  changeoffsetdate   giorno From  lastweek Where  datepart weekday  giorno    1      if  dt utc  gt   changeoffsetdate      Begin          Set  offset since   1      End Else Begin         Set  offset since   2     End End  JMP   if  current offset  lt   offset since Begin     Set  offset   1 End Else if  current offset  gt   offset since Set  offset   -1 Else Set  offset   0  Return  offset  END   Then the function that convert date  CREATE FUNCTION  dbo   UF CONVERT         dt utc datetime2 7    RETURNS datetime AS BEGIN      declare  offset int       Select  offset   dbo UF ADJUST OFFSET  dt utc       if  dt utc  gt    9999-12-31 22 59 59 9999999          set  dt utc    9999-12-31 23 59 59 9999999      Else         set  dt utc    SELECT DATEADD mi  DATEDIFF mi  GETUTCDATE    GETDATE      dt utc         if  offset  lt  gt  0         Set  dt utc   dateadd hh   offset   dt utc       RETURN  dt utc  END

User · Answer

Well if you store the data as UTC date in the database you can do something as simple as  select    MyUtcDate    getdate   - getutcdate   from  dbo   mytable    this was it s always local from the point of the server and you are not fumbling with AT TIME ZONE  your time zone name   if your database get moved to another time zone like a client installation a hard coded time zone might bite you

User · Answer

If enabling CLR on your database is an option as well as using the sql server s timezone  it can be written in  Net quite easily   public partial class UserDefinedFunctions        Microsoft SqlServer Server SqlFunction      public static SqlDateTime fn GetLocalFromUTC SqlDateTime UTC                if  UTC IsNull              return UTC           return new SqlDateTime UTC Value ToLocalTime               A UTC datetime value goes in and the local datetime value relative to the server comes out  Null values return null

[sql] Convert Datetime column from UTC to local time in select statement

Examples related to sql

Examples related to sql-server