UTC Time conversion in SQL Server 2008

Question

Post reply

UTC Time conversion in SQL Server 2008

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
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..

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

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

bjhogan SSC Eights! Points: 870 More actions · Answer 1

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

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 2

Hi

I'm looking for both..

Need some examples to know, how its really works.

roi.reuven SSCommitted Points: 1700 More actions · Answer 3

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)

bjhogan SSC Eights! Points: 870 More actions · Answer 4

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.