Post reply

UTC Time conversion in SQL Server 2008

  • November 30, 2010 at 11:51 pm

    #232063

    Hi

    I want to know how UTC Time conversion is done in SQL Server 2008.

    Any links which I can refer is appreciated..

    I did'nt get a proper link from Google..

  • December 9, 2010 at 7:21 am

    #1260556

    Are you asking how SQL internally calculates UTC based on a given date? Or, are you looking for some examples?

  • December 10, 2010 at 12:19 am

    #1260900

    Hi

    I'm looking for both..

    Need some examples to know, how its really works.

  • December 10, 2010 at 3:27 am

    #1260948

    You can use GETUTCDATE() function to get UTC datetime Probably you can select difference between GETUTCDATE() and GETDATE() and use this difference to ajust your dates to UTC

    you now that it is much easier to control right datetime in the business layer (in .NET, for example).

    eplace "YOUR_DATE" with your date coulmn:

    select dateadd(second,datediff(second,getutcdate(),getdate()),YOUR_DATE)

  • December 13, 2010 at 6:33 am

    #1261660

    The example provided by ROI is good and will definately get you started. As for how SQL Server does it, I don't know the exact way, but my guess is through the time.h library in the code. Since SQL Server is written in C/C++, I would imagine that it takes advantage of the built in libraries along with the timezone and timezone offset information in the registry.

    However, after doing some digging I found this on msdn:

    SQL Server 2008 R2 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

    http://msdn.microsoft.com/en-us/library/ms186724.aspx

    Unless there is someone on here that is willing to divulge the source code, I don't know that we would ever know how the getutcdate function truly works. I did try running "select object_definition(object_id('getutcdate'))" but it just returned null.

    Hope some of this helps.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply