Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to store old dates in SQL Server

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?

like image 657
Rick Avatar asked Feb 04 '09 15:02

Rick


2 Answers

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.

like image 132
bdukes Avatar answered Nov 04 '22 04:11

bdukes


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.

like image 42
Misko Avatar answered Nov 04 '22 03:11

Misko