Old dates

11 Feb 2006 7:37 AM

Mark Rae

Hi,

Sorry if this is a frequently-asked question, but I'm about to start
designing a database for cataloguing historical artefacts and documents,
many of which are from much earlier than the SQL Server minimum value for a
datetime field of 1753.

I've done a bit of a trawl on Google and elsewhere, and have come across
various suggestions such as:

1) adding a multiple of 400 years (to account for leap years) to every date
and then subtracting this at the front-end

2) storing the dates as ints instead of datetimes e.g. Magna Carta would be
stored as 12150615

3) storing the dates as three separate fields, one for year, month and day
(fractions of days are not required in this system)

All of the above provide a solution of sorts. However, I have three problems
which none of them addresses properly:

1) I need to be able to calculate the day of the week from any given day

2) I need to support the change from the Julian to the Gregorian calendar
i.e. Wednesday 2 September 1752 being followed immediately by Thursday 14
September 1752.

3) BC dates e.g. 1 January 1AD being preceded immediately by 31 December 1BC

I'd be very interested to know how other people have addressed this issue,
not wishing to re-invent the wheel.

Any assistance gratefully received.

Mark

11 Feb 2006 10:35 AM

David Portas

Mark Rae wrote:
Show quote

> Hi,
>
> Sorry if this is a frequently-asked question, but I'm about to start
> designing a database for cataloguing historical artefacts and documents,
> many of which are from much earlier than the SQL Server minimum value for a
> datetime field of 1753.
>
> I've done a bit of a trawl on Google and elsewhere, and have come across
> various suggestions such as:
>
> 1) adding a multiple of 400 years (to account for leap years) to every date
> and then subtracting this at the front-end
>
> 2) storing the dates as ints instead of datetimes e.g. Magna Carta would be
> stored as 12150615
>
> 3) storing the dates as three separate fields, one for year, month and day
> (fractions of days are not required in this system)
>
> All of the above provide a solution of sorts. However, I have three problems
> which none of them addresses properly:
>
> 1) I need to be able to calculate the day of the week from any given day
>
> 2) I need to support the change from the Julian to the Gregorian calendar
> i.e. Wednesday 2 September 1752 being followed immediately by Thursday 14
> September 1752.
>
> 3) BC dates e.g. 1 January 1AD being preceded immediately by 31 December 1BC
>
> I'd be very interested to know how other people have addressed this issue,
> not wishing to re-invent the wheel.
>
> Any assistance gratefully received.
>
> Mark

I suggest you create your own calendar table (one row per date)
including the day of the week. Something like:

CREATE TABLE calendar (dt INTEGER NOT NULL CHECK (dt BETWEEN -40000101
AND 21000101 /* YYYYMMDD */) PRIMARY KEY, day_of_week VARCHAR(9) NOT
NULL);

Use dt as the date in your other tables.

I assume you realise that your requirements regarding September 1752
are only valid for Britain and the British colonies of that time. Also,
the date of the beginning of the year wasn't always 1 January in the
Julian calendar - that's dependent on culture, location and time. For
these reasons among others there isn't a single standard solution.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

11 Feb 2006 8:42 PM

Erland Sommarskog

David Portas (REMOVE_BEFORE_REPLYING_dpor***@acm.org) writes:

> I assume you realise that your requirements regarding September 1752
> are only valid for Britain and the British colonies of that time. Also,
> the date of the beginning of the year wasn't always 1 January in the
> Julian calendar - that's dependent on culture, location and time. For
> these reasons among others there isn't a single standard solution.

And don't forget that the first year where 29 Feb appeared as it should
according the Julian Calendar was 12 AD. 1BC, 4AD and 8AD were
not leap years. Before that they fell every third year, because of some
misunderstanding.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Feb 2006 8:53 AM

Mark Rae

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9767DCC5AF65EYazorman@127.0.0.1...

> And don't forget that the first year where 29 Feb appeared as it should
> according the Julian Calendar was 12 AD. 1BC, 4AD and 8AD were
> not leap years. Before that they fell every third year, because of some
> misunderstanding.

Thanks.

12 Feb 2006 8:53 AM

Mark Rae

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1139654142.805930.98630@f14g2000cwb.googlegroups.com...

> I suggest you create your own calendar table (one row per date)
> including the day of the week.

I see.

> I assume you realise that your requirements regarding September 1752
> are only valid for Britain and the British colonies of that time.

Yes.

> Also, the date of the beginning of the year wasn't always 1 January in the
> Julian calendar - that's dependent on culture, location and time.

That's not such an issue.