CODEWITHSUNDEEP
sqlsql-serversql-server-2005datetimedate
Rick
Rick

Reputation: 1913

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?

Upvotes: 11

Views: 5073

Answers (8)

RBerman
RBerman

Reputation: 391

One way to store dates as old as 1/1/4713 BC (and right through modern times) is to use Julian Day. This is not the same as Julian date, and is an integer encoding the number of days since 1/1/4713 BC.

For simplicity, here are conversions from date to Julian Day and back. Note that you still can't convert a Julian Day to a DATE if it would exceed the range of the DATE type, but as bdukes says above, DATE should hold back to 1/1/0001.

IF OBJECT_ID (N'dbo.ufn_JulianDayFromDate', N'FN') IS NULL
    exec('CREATE function [dbo].[ufn_JulianDayFromDate] () returns int As begin 
return 1 end;');
go

alter function dbo.ufn_JulianDayFromDate(@theDate as date) returns int
as 
begin
    declare @JulianDayBase int=693596;
    return @JulianDayBase + datediff(d, 0, @theDate);
end;
go

and

IF OBJECT_ID (N'dbo.ufn_DateFromJulianDay', N'FN') IS NULL
    exec('CREATE function [dbo].[ufn_DateFromJulianDay] () returns int As begin return 1 end;');
go

alter function dbo.ufn_DateFromJulianDay(@JulianDay as int) returns date
as 
begin
    declare @JulianDayBase int=693596;
    return dateadd(d, @JulianDay-@JulianDayBase, '1/1/1900')
end;

go

Upvotes: 0

Rad
Rad

Reputation: 8381

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

Upvotes: 1

Alan
Alan

Reputation: 3895

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.

Upvotes: 1

Ian Varley
Ian Varley

Reputation: 9457

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.

Upvotes: 4

Joshua
Joshua

Reputation: 43270

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

Upvotes: 1

bdukes
bdukes

Reputation: 155935

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.

Upvotes: 15

Treb
Treb

Reputation: 20271

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 ;-)

Upvotes: 1

Misko
Misko

Reputation: 2044

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.

Upvotes: 4

Related Questions