It’s a date!

Microsoft SQL Server Date and Time

One of the advantages of a database is that you can restrict the type of data that is entered.  Unlike a spreadsheet that lets you enter any kind of data, such as text, a smiley face or a date, SQL Server will force you to enter the correct type of data in a specific field.

This may sound like a disadvantage but it’s not.  Flexibility seems like a good thing, but you pay a heavy price. The old adage of "Garbage in; garbage out" is so true. A database makes sure the right information gets entered into the right field at the point of entry.  When you want to analyse the information later, it actually makes sense. Comparisons, selections and reports are correct, instead of finding that half your data is incorrect.

For example, SQL Server has six different data types for date and time:

Data type	Date range	Time range	Storage space
datetime	01-Jan-1753 to 31-Dec-9999	00:00:00 through 23:59:59.997	8 bytes
smalldatetime	01-Jan-1900 to 06-Jun-2079	00:00:00 through 23:59:59	4 bytes
datetime2	01-Jan-001 to 31-Dec-9999	00:00:00 through 23:59:59.9999999	6 bytes for precisions less than 3 7 bytes for precisions 3 and 4 8 bytes for all other precisions 7 is the default if precision not stated
date	01-Jan-001 to 31-Dec-9999	-
datetimeoffset	01-Jan-001 to 31-Dec-9999	00:00:00 through 23:59:59.9999999 Time zone offset range -14:00 to +14:00	10 bytes
time	-	00:00:00.0000000 through 23:59:59.9999999	5 bytes

That gives you options, depending on the type of data you want to hold.  But you have to be careful.  Datetime, for example, may be suitable for many purposes, but if you want to record historical facts.  The reason is simple - you can’t enter a year earlier than 1753.

So why would a date data type start at 1753? Is it just an arbitrary date so long ago that it surely must cover everything? I haven’t asked the SQL Server team, and maybe I should, but my guess is that it was when the Gregorian calendar was introduced to Britain and parts of the USA.  Interestingly, at the time of the switchover from the Julian calendar to the Gregorian calendar people often used two dates, so as to be clear.  Quite sensibly, SQL Server doesn't allow that, but it does allow you to choose the right date/time data type for your needs.

Choosing the correct data type for your data and storage needs will immediately improve the quality of your data. Whether its a date, a smiley face or text ...