Best way to store old dates in SQL Server

Question

What is the best/most efficient way to store old dates (pre-1753) in SQL Server 2005? I am not concerned with storing times - just dates. SQL Server's datetime data type can only hold dates back to January 1, 1753. The MSDN documentation states that there are date and datetime2 data types, but SQL Server Management Studio does not seem to support them (Error: invalid data type).

How inefficient would it be to store dates as strings or ints of the form "YYYYMMDD"? I do a lot of querying and sorting on two date fields in my table (StartDate and EndDate).

UPDATE:

There have been some suggestions below to store year, month, and date in separate fields. What is the benefit of storing the parts in different fields rather than in a single integer field?

Answer 1

I've never done this but maybe you could store the date as an integer representing number of days since whatever minimum date suits you. Then you could either create a lookup table that maps those integers to a year, month, and day, or you could write user defined functions to convert from an integer to a date or vice versa.

That should be fairly efficient in terms of selecting and sorting.

Answer 2

Strings would probably be less efficient than just storing integers for the year, month and day. That's a little more verbiage in your queries, but they'll probably run faster as you can index them in ways that make sense for the kinds of queries you're doing.

So for example:

CREATE TABLE myOldDates (
  year INT,
  month INT,
  day INT,
  -- otherstuff ...
)

Then queries would all be like:

-- get records between 5/15/1752 and 3/19/1754
SELECT * FROM myOldDates
  WHERE 
    (year = 1752 AND ((month = 5 and day >= 15) or month > 5) OR year > 1752)
    AND (year = 1754 AND ((month = 3 and day <= 19) or month < 3) OR year < 1754)

That is ugly, to be sure, but that's about as ugly as it gets for range queries, so once you write it the first time, you can encapsulate it in a function.

Answer 3

One problem with storing dates in YYYYMMDD format is that you could end up with dates that do not exist (e.g. 16000231 - Febuary 31 does not exist). You would need to do some validation client side, before entering it into the db.

The same is of couse true for storing the date in year, month and day integers, as proposed by Ian Varley. But aisde from that I like his answer and just wish I would have thought of it ;-)

Answer 4

The date type is definitely what you want to use. It's range is "January 1, 1 A.D. through December 31, 9999 A.D." It also just stores date information, without the time part.

Are you, perhaps, using SSMS 2005, rather than 2008, or connected to a 2005 instance? That type was introduced in SQL Server 2008. If you have the ability to use a 2008 database, I would think that it unquestionably the way to go.

Answer 5

YYYYMMDD = 8 bytes. You could cut it down to 4 bytes with SMALLINT and TINYINT using 3 columns.

Answer 6

Using ints as suggested by CodeMonkey1 seems like a good idea, and will make it easier to do "date math" (e.g., some date + XX days).

Write some UDFs (also as advised by CodeMonkey1) to convert int --> YYYYMMDD --> int and you'll have the flexibility that Ian Varley mentions in his answer.

Answer 7

An idea -- if you have some .NET knowledge you could create a CLR type to store the date, that would essentially be a datetime. If you are doing a lot of calculations with the date rather than simple queries, it might be something to investigate