CODEWITHSUNDEEP
sql-server-2005timezone
Amessihel
Amessihel

Reputation: 6394

Retrieving the timezone of a recorded datetime in SQL Server (2005)

Let testdate be a table with a datetime column d. I executed this sql request a few times and changed the OS timezone between each execution.

INSERT INTO [testdate] ([d])
     VALUES (CAST(GETDATE() AS DATETIME))

I got this result :

     ________________________
    | d (datetime)           |
.---|------------------------|
| 1 | 2016-09-08 15:15:28.847|
| 2 | 2016-09-08 18:15:45.407|
| 3 | 2016-09-08 11:17:23.317|
°----------------------------°

The first was played at GMT+6, second at GMT+9, last at GMT+1 (summer time). I believed I'll get all these rows at current timezone.

Are these datetime values stored as a "snapshot" ? Or is there a way to get the timezone for each value ?

Upvotes: 1

Views: 199

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

The timezone is not stored with the date value. You can read this article: Solving the Datetime Mystery:

So how does SQL Server internally store the dates? It uses 8 bytes to store a datetime value—the first 4 for the date and the second 4 for the time. SQL Server can interpret both sets of 4 bytes as integers. For the date portion, the value SQL Server stores is the number of days before or after a base date of January 1, 1900. Because of this storage protocol, SQL Server assumed the date of January 1, 1900, when I didn't supply the date in my first example. SQL Server internally stored a value of 0. A negative number represents a date earlier than January 1, 1900.

SQL Server stores the second integer for the time as the number of clock ticks after midnight. A second contains 300 ticks, so a tick equals 3.3 milliseconds (ms). You can see the values for days and clock ticks by converting a datetime value to a binary(8) value and using the substring function to extract each set of 4 bytes. The code in Figure 3 then converts each set of 4 bytes into an integer.

You can use the datetime datatype and store the dates in UTC timezone and then you can format the date while retrieving it from the database in the format in which you want.

Upvotes: 1

Related Questions