Old dates

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 w 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.
MarkMark Rae wrote:
> 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 dat
e
> and then subtracting this at the front-end
> 2) storing the dates as ints instead of datetimes e.g. Magna Carta would b
e
> 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 proble
ms
> which none of them addresses properly:
> 1) I need to be able to calculate the day of the w 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 1
BC
> 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 w. Something like:
CREATE TABLE calendar (dt INTEGER NOT NULL CHECK (dt BETWEEN -40000101
AND 21000101 /* YYYYMMDD */) PRIMARY KEY, day_of_w 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
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.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 w.
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.|||"Erland Sommarskog" <esquel@.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.