Dates prior to 1753-01-01

Hello,

I have an Access DB with a column [date] holding dates from present day back
to around year 1702, and I am trying to run DTS Import with this MDB, which
fails due to a conversion error.

MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thus
I am under the impression having the [date] field as varchar is the only way
to store the dates.

My questions are:

a) Can dates prior to 1753-01-01 be stored in a date column?

b) If dates are in a column of type varchar (or similar) is it possible to
sort them chronologically with T-SQL ?

c) 1753-01-01 seems particularly arbitrary, any reason for this?

Many thanks for any help, it's driving me mad!
Johna) No

b) Yes. If you store them as most-siginificant digits first (YYYYMMDD) you
can safely sort as for any other VARCHAR.

c) 1752 was when Britain and its colonies (including parts of America)
switched to the Gregorian calendar. The problem with much earlier dates is
that date validation, arithmetic and comparisons don't make sense unless you
put the date in the context of a particular calendar and location. For
example, Access allows a date of 1752-09-04 - a date which never happened at
all in Britain but was valid for other parts of the world. Rather than try
to make sense of this SQL Server takes the easy way out, from a US point of
view, by taking 1753 as its calendar starting date.

Take a look at:
http://www.tondering.dk/claus/calendar.html

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> c) 1752 was when Britain and its colonies (including parts of America)
> switched to the Gregorian calendar. The problem with much earlier dates
> is that date validation, arithmetic and comparisons don't make sense
> unless you put the date in the context of a particular calendar and
> location.

From a pedantic point of view, this applies to later date as well. The
Orthodox world did not change until around 1918. That's how got an
October revolution that is celebrated in November. ("Is?", the next pedant
may say now. The sad story is that there are still some people thinks
that are reason to celebrate this event.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Google up a hisotry of the calendar, it is fun!

However, for your problem, Google up "Julian dates". It is a large
integer used by astronomers to avoid calendar problems. It can be
converted back to the Common Era (aka Gregorian or Christian) calendar
with a bit of code.|||Cheers for the help.
John

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zqudnREkRYdsb0_fRVn-gg@.giganews.com...
> a) No
> b) Yes. If you store them as most-siginificant digits first (YYYYMMDD) you
> can safely sort as for any other VARCHAR.
> c) 1752 was when Britain and its colonies (including parts of America)
> switched to the Gregorian calendar. The problem with much earlier dates is
> that date validation, arithmetic and comparisons don't make sense unless
you
> put the date in the context of a particular calendar and location. For
> example, Access allows a date of 1752-09-04 - a date which never happened
at
> all in Britain but was valid for other parts of the world. Rather than try
> to make sense of this SQL Server takes the easy way out, from a US point
of
> view, by taking 1753 as its calendar starting date.
> Take a look at:
> http://www.tondering.dk/claus/calendar.html
> --
> David Portas
> SQL Server MVP
> --|||Pedants' Calendar:
http://www.douglasadams.com/dna/pedants.html

;-)

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Pedants' Calendar:
> http://www.douglasadams.com/dna/pedants.html
> ;-)

Hehe, that was a good one!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp