Get current time and deal with daylight saving time for a particular timezone

Question

I am having trouble of using datetime in SQL Server 2012, my SQL database is hosted on a server that is using different timezone than the timezone I would like to use for my application, also my timezone I want to use is under daylight saving time, so I have to change the code every time this (daylight saving time) happens

So if I use:

SELECT Getdate()

I will get current date and time of the server (wherever your database is hosted from), the result will be something like :

2015-12-23 09:09:40.303

Is there anyway or a built-in method in SQL that I can use to retrieve current date and time of particular timezone that also observes daylight saving time, or specifically in this case it is Central Standard Time (for USA), I would like it to take care of the daylight saving time automatically instead of having to manually modify the code every time the daylight saving time occurs.

Wouldn't be better to store all your data in UTC, and use GetUtcDate() ? — ken2k, Dec 23 '15 at 16:27
Why particularly you want this in sql /database end.easily achievable from c# code.In .net 3.5 there is a class called `TimeZoneInfo`.So idea is getting UTC and adjust the offset like `TimeZoneInfo.ConvertTimeBySystemTimeZoneId( DateTime.UtcNow, "Central Standard Time");` — Navoneel Talukdar, Dec 23 '15 at 16:28
This post might help: http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement — Chris Stillwell, Dec 23 '15 at 16:29
Use your application code to generate the necessary dates and times and send them to your db as parameters when required. — Dan Bracuk, Dec 23 '15 at 17:50

Navoneel Talukdar · Answer 1 · 2015-12-23T16:40:31.123

1

Ok reiterating my comments again I would say that you can get UTC time first then get the actual time information for zone.

So something like this could server the purpose-

TimeZoneInfo centralUSAZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");

DateTime centralUSATime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, centralUSAZone);

ConvertTimeFromUtc Function essentially takes two parameters.

dateTime Type: System.DateTime

The Coordinated Universal Time (UTC).

destinationTimeZone Type: System.TimeZoneInfo

The time zone to convert dateTime to.

Have a detailed look on msdn doc.https://msdn.microsoft.com/en-us/library/system.timezoneinfo.converttimefromutc.aspx.

edited Dec 23 '15 at 16:40

answered Dec 23 '15 at 16:35

Navoneel Talukdar

4,393
5
21
42

Thanks a lot! but I think I've asked this question before for the C#: http://stackoverflow.com/questions/32657578 , so basically I can retrieve the current time and assign it to a variable on C# without any problem but I am having trouble to pass it to SQL query, I may have to ask another question, in the meantime this question is looking for a solution in SQL. – Ronaldinho Learn Coding Dec 23 '15 at 16:43
Then edit this question more and tell us actual problem you are facing. – Navoneel Talukdar Dec 23 '15 at 16:45
I think I will create another question for that, I will leave this question as-is to find a solution in SQL that doesn't need to use .NET resources, someone else may use PHP or other language and want to know, right. – Ronaldinho Learn Coding Dec 23 '15 at 16:51

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

You can offset from one timezone to another using SWITCHOFFSET. But the second parameter requires you to adjust for daylight savings.

From MSDN:

time_zone Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

There is a discussion on Stack DBA that might help you. TL/DR: Using the CLR was the most popular answer.

Non-Daylight Saving Example:

SELECT
    SYSDATETIMEOFFSET()                                AS Server_Date_Time_WithTZone,
    SWITCHOFFSET(SYSDATETIMEOFFSET(), '+04:00')        AS NewTZone
;

score 0 · Answer 3 · answered Jan 27 '17 at 23:32

Unfortunately, timezones can get really hard in the US because of Daylight Savings Time. You should probably use a library or CLR function, but if you cannot, here is a naive SQL-only implementation. It's naive because:

It assumes US only rules (DST is 2AM on some pre-defined Sunday, etc).
It assumes you don't have dates prior to 1970
It assumes you know the local timezone offsets (i.e.: EST=-05:00, EDT=-04:00, etc.)

Here's the SQL:

-- make a table (#dst) of years 1970-2101. Note that DST could change in the future and
-- everything was all custom and jacked before 1970 in the US.
declare @first_year varchar(4) = '1970'
declare @last_year varchar(4) = '2101'

-- make a table of all the years desired
if object_id('tempdb..#years') is not null drop table #years
;with cte as (
    select cast(@first_year as int) as int_year
          ,@first_year as str_year
          ,cast(@first_year + '-01-01' as datetime) as start_of_year
    union all
    select int_year + 1
          ,cast(int_year + 1 as varchar(4))
          ,dateadd(year, 1, start_of_year)
    from cte
    where int_year + 1 <= @last_year
)
select *
into #years
from cte
option (maxrecursion 500);

-- make a staging table of all the important DST dates each year
if object_id('tempdb..#dst_stage') is not null drop table #dst_stage
select dst_date
      ,time_period
      ,int_year
      ,row_number() over (order by dst_date) as ordinal
into #dst_stage
from (
    -- start of year
    select y.start_of_year as dst_date
          ,'start of year' as time_period
          ,int_year
    from #years y

    union all
    select dateadd(year, 1, y.start_of_year)
          ,'start of year' as time_period
          ,int_year
    from #years y
    where y.str_year = @last_year

    -- start of dst
    union all
    select
        case
            when y.int_year >= 2007 then
                -- second sunday in march
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-03-08')) + 1) % 7, y.str_year + '-03-08')
            when y.int_year between 1987 and 2006 then
                -- first sunday in april
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-01')) + 1) % 7, y.str_year + '-04-01')
            when y.int_year = 1974 then
                -- special case
                cast('1974-01-06' as datetime)
            when y.int_year = 1975 then
                -- special case
                cast('1975-02-23' as datetime)
            else
                -- last sunday in april
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-04-24')) + 1) % 7, y.str_year + '-04-24')
        end
        ,'start of dst' as time_period
        ,int_year
    from #years y

    -- end of dst
    union all
    select
        case
            when y.int_year >= 2007 then
                -- first sunday in november
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-11-01')) + 1) % 7, y.str_year + '-11-01')
            else
                -- last sunday in october
                dateadd(day, ((7 - datepart(weekday, y.str_year + '-10-25')) + 1) % 7, y.str_year + '-10-25')
        end
        ,'end of dst' as time_period
        ,int_year
    from #years y
) y
order by 1

-- assemble a final table
if object_id('tempdb..#dst') is not null drop table #dst
select a.dst_date +
          case
             when a.time_period = 'start of dst' then ' 03:00'
             when a.time_period = 'end of dst' then ' 02:00'
             else ' 00:00'
          end as start_date
      ,b.dst_date +
          case
             when b.time_period = 'start of dst' then ' 02:00'
             when b.time_period = 'end of dst' then ' 01:00'
             else ' 00:00'
          end as end_date
      ,cast(case when a.time_period = 'start of dst' then 1 else 0 end as bit) as is_dst
      ,cast(0 as bit) as is_ambiguous
      ,cast(0 as bit) as is_invalid
into #dst
from #dst_stage a
join #dst_stage b on a.ordinal + 1 = b.ordinal
union all
select a.dst_date + ' 02:00' as start_date
      ,a.dst_date + ' 03:00' as end_date
      ,cast(1 as bit) as is_dst
      ,cast(0 as bit) as is_ambiguous
      ,cast(1 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'start of dst'
union all
select a.dst_date + ' 01:00' as start_date
      ,a.dst_date + ' 02:00' as end_date
      ,cast(0 as bit) as is_dst
      ,cast(1 as bit) as is_ambiguous
      ,cast(0 as bit) as is_invalid
from #dst_stage a
where a.time_period = 'end of dst'
order by 1

-------------------------------------------------------------------------------

-- Test Eastern
select
    the_date as eastern_local
    ,todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end) as eastern_local_tz
    ,switchoffset(todatetimeoffset(the_date, case when b.is_dst = 1 then '-04:00' else '-05:00' end), '+00:00') as utc_tz
    --,b.*
from (
    select cast('2015-03-08' as datetime) as the_date
    union all select cast('2015-03-08 02:30' as datetime) as the_date
    union all select cast('2015-03-08 13:00' as datetime) as the_date
    union all select cast('2015-11-01 01:30' as datetime) as the_date
    union all select cast('2015-11-01 03:00' as datetime) as the_date
) a left join
#dst b on b.start_date <= a.the_date and a.the_date < b.end_date

Get current time and deal with daylight saving time for a particular timezone

3 Answers3