none
How to find the Eastern Time after Daylight Saving Time difference i.e, (GMT - 4) or (GMT - 5)

    Question

  • Hi All,

    I am using SQL Server 2005.

    I have a requirement to find the Eastern Time,

    Which is (GMT - 4) during Daylight Saving Time(DST)

    and (GMT - 5) during non DST.

     

    Can anyone help to develop a logic without hard coding any date and time values, to find the Eastern Time(EST)?

    Either in SP or SQL Function.

     

    Thanks in Advance...

    Biswa


    bplthebest
    Monday, November 08, 2010 6:43 AM

Answers

  • Thanks all, I found the solution by consuming the c# dll which calculates the DST. And consumed the dll in SQL Server 2005.

     

    Will share the code shortly .. :)

     

    Cheers,

    Biswa


    bplthebest
    • Proposed as answer by Naomi NModerator Monday, November 08, 2010 1:06 PM
    • Marked as answer by KJian_ Monday, November 15, 2010 7:09 AM
    Monday, November 08, 2010 12:59 PM

All replies

  • some thing like this??

    create proc getChangedTime
    (
    	@DST bit
    )
    as
    begin
    	if(@DST = 0)
    		select DATEADD(hour,-5,getutcdate())
    	else
    		select DATEADD(hour,-4,getutcdate())				
    end
    
    
    Monday, November 08, 2010 7:12 AM
  • This is fine. I need to know the way to find out, when is the  DST start and End date for a particular year.

     

    Regards,

    Biswa

     

     


    bplthebest
    Monday, November 08, 2010 7:25 AM
  • Hi Biswa,

    That is a lot harder than it sounds. You could of course hard-code the
    rules for the DST switch in a function or stored procedure, but those
    rules are not immutable. For example, they changed a few years ago in
    the US.

    If the rules change, MS will include them in their .Net library and
    push the change through auto-update to all computers running Windows.
    Your best bet would therefor be to leverage the date/time function in
    the .Net framework. To do that from within SQL Server, you need to
    create a CLR user-defined function.

    If you need help with that, I suggest you go to the forum that deals
    with CLR in SQL Server. I know too little about this feature to be of
    assistance.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Monday, November 08, 2010 8:25 AM
  • DST state date information is stored in the Registry as a BINARY value.

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\DaylightStart




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Monday, November 08, 2010 8:42 AM
  • Thanks all, I found the solution by consuming the c# dll which calculates the DST. And consumed the dll in SQL Server 2005.

     

    Will share the code shortly .. :)

     

    Cheers,

    Biswa


    bplthebest
    • Proposed as answer by Naomi NModerator Monday, November 08, 2010 1:06 PM
    • Marked as answer by KJian_ Monday, November 15, 2010 7:09 AM
    Monday, November 08, 2010 12:59 PM