SQL Server 2000 and UTC

Question

SQL Server 2000 and UTC

Craig Carns

Valued Member

Points: 63
More actions
February 16, 2005 at 5:39 pm

#163352

I have read a lot of theory on how you should store DATETIME values as UTC(GMT) in the database if your application/db is going to be utlized across muliple time zones.
However I also read that SQL Server really stores these values as UTC, but always converts them to your servers local timezone upon display/query.
Here is what is hurting my brain: Do I need to care about that or not, because it seems like the dates will actually be stored incorrectly. Example:
I am in PST timezone (at the moment -8.00 hours) so 1AM PST = 9AM UTC.
Step A) A user types in 1PM PST to one of my DATETIME fields in my application.
Step B) Now my INSERT statement in my stored procedures has a function that converts that DATETIME to UTC.
1) At that point isn't the DATETIME being stored in the database really being stored as 5PM?
2) Do I really care as long as I know it is coming out as UTC?

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

As far as I know there are no timezone conversions in storing and retrieving datetime data in SQL Server. What a good client application will do is use the local machines regional settings to format the returned data appropriately. eg dd/mm/yyyy or mm/dd/yyyy

You could easily confirm this by inserting two dates (one using local time and one converted to UTC) into a temp table, then just select the two dates without any conversion to see whats stored and presented.

--------------------
Colt 45 - the original point and click interface

Craig Carns Valued Member Points: 63 More actions · Answer 2

Looks like the only clean solution is to store the DATETIME fields as UTC and have the consuming application do all the conversion (mine happens to be ASP.NET). One major reason is the UTC functionality in SQL Server is very limimted. EXAMPLE: (this should query the time difference between UTC and my local PST)

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

DECLARE @dt_Date_Time as datetime

SET @dt_Date_Time = '4/3/2005 00:00:00'

select DATEADD ( hh , (DATEDIFF ( hh , GetDate(), GetUTCDate() )) , @dt_Date_Time )

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

The result of the difference is always based on the offset of the time it was ran, no easy way without adding a timezone table to accomplish this.

QUESTION: Since I will be converting the time to UTC upon INSERT/UPDATE and the database thinks I am using local time, couldn't I have issues if:

1) I picked up the database and moved it to a new timezone?

2) Have issues with the time if the date entered falls into the magical hour of daylight saving time switch?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

I would be interested to know where you have read that SQL Server automatically stores dates as UTC dates? Never heard of this before. You might want to read this: ttp://www.karaszi.com/sqlserver/info_datetime.asp

Tibor now has a link at the end of his article back to the homepage of Rick Snodgrass. You can download his great book "Developing time-oriented database applications in SQL" there for free.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Craig Carns Valued Member Points: 63 More actions · Answer 4

Thanks a lor Frank for the referrals to more information.

As far as me reading about SQL server storing values as UTC I am not sure where I read that- I have read a lot the past few days on the subject.

However if you think about it, the statement it probably half true. Since the dates are stored numerically based off a given starting point/date, it would make sense the starting point is a UTC 0 starting point not a changeable time zone specfic starting point which would be different from time zone to time zone, but I am speculating.