tsql getdate conversion

Question

Hi,

I know that when i do the following, it converts getdate to int

select cast (getdate() as int)

Getdate output on my server is "2010-06-11 14:42:20.100" and the int to which the above command is converting to is 40339. What is this integer? Did this int consider minutes and seconds? i am confused. Please help.

Regards

Manjot

Answer 1

This number is the number of days since the reference date, which is 01/01/1900. You should get the same result from:

SELECT DATEDIFF(day, 0, GETDATE())  

Internally, SQL stores datetimes as two 4-byte integers. The first integer (which you are getting in your statement) represents the number of days since the reference date. The second integer represents the number of 1/300 second intervals since midnight. You can find the second integer by converting first to binary, then to int:

SELECT CONVERT(int, CONVERT(binary(4), GETDATE()))