SQL Server Tips by Gama and Naughter

Smalldatetime

This is a 32 bit data type, the 16 bits on the left represent an unsigned integer which is the number of days after January 1, 1900. The  16 bits on the right represent the number of minutes since midnight. Values under 29.998 are rounded down to the nearest minute, values higher than that are rounded up.

Hexadecimal values and the corresponding date

0x00000000  Jan  1 1900 12:00AM

0xffff059f  Jun  6 2079 11:59PM

0x00000001  Jan  1 1900 12:01AM

0x00010000  Jan  2 1900 12:00AM

Smalldatetime can represent dates up to June 6, 2079. The time part can go from 12:00AM (0x0000) to 11:59PM (0x059f). 0x059f is 24*60-1 (24 hours times 60 minutes) in hexadecimal.

Datetime
 

This is a 64 bit integer, the 32 bits on the left represent the number of days before or after January 1, 1900. The 32 bits on the right represent the number of 1/300th of a second since midnight.

Hexadecimal values and the corresponding date

0x0000000000000000      Jan  1 1900 12:00AM

0x00000000018B81FF      Jan  1 1900 11:59PM

0xFFFF2E4600000000      Jan  1 1753 12:00AM

0x002D247F00000000      Dec 31 9999 12:00AM

Datetime can represent dates up to December 31, 9999. The time part can go from 12:00AM (0x0000000000000000) to 11:59PM (0x00000000018B81FF). 018B81FF is 24*60*60*300-1 (24 hours times 60 minutes times 60 seconds times 300) in hexadecimal.

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm