How SQL Programmers Choose their Dates

(I just created this blog and been thinking where to start. I decided to start with one of the basic data types. The, sometimes confusing, Datetime and its younger sibling, SmallDateTime Data Types. I hope this notes will be able to help you understand the similarities and differences between these two data types)

As of SQL 2005, there are only two data types that handles both date and time data: DateTime and SmallDateTime. There was no type that handles neither date only, nor time only. Both data types handles data with a date part combined with a time of the day.

Instead of repeating what has been posted on articles around the net and Book Onlines, I think it would be better to just give the highlights.

SmallDateTime	DateTime
Minimum Value
January 1, 1900 00:00:00	January 1, 1753 00:00:00.000
Maximum Value
June 6, 2079 59:59:00	December 31, 9999 59:59:59.997
Precision
Up to 3.333 millisenconds	Up to one minute
Storage Size
8 bytes (Two 4-byte int)	4 bytes (Two 2-byte int)
Accuracy
Rounds to increments of .000, .003, .007	Up to 29.998 seconds are rounded down, all else round up
Default Value (base date)
January 1, 1900 00:00:00	January 1, 1900 00:00:00

It's a common misconception that SQL Server stores these data type in some sort of date-structured formats. Internally, it's being stored as a two-part integer. DateTime, being able to handle higher precision, stores data in a two 4-byte integer. The first four stores the number of days before or after the base date. The other four stores the number of milliseconds since midnight. SmallDateTime stores data as a two 2-byte integer. The first two stores the number of days after the base date, the other two stores the number of minutes since midnight.


How to choose?

The answer would depend on the range of value that will be processed. Based on the table above, SmallDateTime can only handle dates from January 1, 1900 through June 6, 2079. If the expected date value is outside of this range, use DateTime.

Some application would only require up to the minute accuracy. For example, most payroll applications calculate number of hours worked up to the minute only, and does not bother considering seconds and milliseconds. In this case, use SmallDateTime. On the other hand, some telecommunication company uses the actual Elapsed Conversation Duration (ECD), that is accurate to the seconds, and sometimes up to milliseconds ,to come up with various calculations of the billable minutes. Some calculations consider a second as a billable unit, others use pulse (6 seconds = 1 pulse). Depending on the application (consider future requirements as well) these sample cases could easily be the major consideration in deciding which type to use.

Once a date data is stored to a SmallDateTime column, the second precision will be gone even if the data type is modified to DateTime later. Only those newly inserted or newly updated values will be affected.



~~CK