Hello: i´m developing an application where must be loaded a lot of historic
al
data, including birth dates before 1753.
I´ve been searching the web and find suggestions like "it´s an input
mistake" or "take dates as string", but i really need to use those fields
like dates.
Can you say me if there is another solution, or tell me why is this
restriction applied in SQL SERVER ? What is the benefit of this restriction 
?
Thanks.

[ Post a follow-up to this message ]

The following link provides a bit of explanation and contains some very good
links at the bottom of the page.

http://aa.usno.navy.mil/data/docs/JulianDate.html

"Danicaban" <Danicaban@discussions.microsoft.com> wrote in message
news:0DE65C3D-3AF6-4685-89C7-06694D145068@microsoft.com...
> Hello: i´m developing an application where must be loaded a lot of
historical
> data, including birth dates before 1753.
> I´ve been searching the web and find suggestions like "it´s an input
> mistake" or "take dates as string", but i really need to use those fields
> like dates.
> Can you say me if there is another solution, or tell me why is this
> restriction applied in SQL SERVER ? What is the benefit of this
restriction ?
> Thanks.

[ Post a follow-up to this message ]

As you have already found, SQL Server doesn't support dates before 1753 in a
DATETIME so I suggest you use INTEGER or VARCHAR instead.

The "benefit" of this restriction is that it insulates the user (and
Microsoft's development team!) from some of the complexities of historical
dates. Date arithmetic and comparisons are hard to do for such ancient dates
because of the many different calendars used in different parts of the world
.

If you are storing dates earlier than 1753 to a precision of more than one
year then you will probably also want to store some contextual information
with the date (i.e. which calendar the date pertains to). Without that
context, a DATETIME datatype for dates is of limited use since it can't
always be meaningfully compared to dates in other calendars in a way that is
easily understood. Microsoft presumably decided that customer demand didn't
justify building such complexities into the product.

For all the messy details on calendars, see:
http://www.tondering.dk/claus/calendar.html

--
David Portas
SQL Server MVP
--

[ Post a follow-up to this message ]

http://groups.google.co.uk/groups?h...lic.sqlserver.*

Is a previous (extensive) discussion on this newsgroup about this topic.

--
Jacco Schalkwijk
SQL Server MVP


"Danicaban" <Danicaban@discussions.microsoft.com> wrote in message
news:0DE65C3D-3AF6-4685-89C7-06694D145068@microsoft.com...
> Hello: i´m developing an application where must be loaded a lot of
> historical
> data, including birth dates before 1753.
> I´ve been searching the web and find suggestions like "it´s an input
> mistake" or "take dates as string", but i really need to use those fields
> like dates.
> Can you say me if there is another solution, or tell me why is this
> restriction applied in SQL SERVER ? What is the benefit of this
> restriction ?
> Thanks.

[ Post a follow-up to this message ]

The datetime field in sql is stored as 2 four byte number, one is the number
of days before or after 1/1/1900, the other the number of milliseconds after
midnight.

The actual limitation is imposed not by storage, but the julian/gregorian
calendar issues.  The big three (DB2, Oracle, SQL) all handle valid date
ranges differently.

If you need to store dates outside of the ranges SQL server supports ( I
have an application that requires this)  I suggest creating a UDD for the
date field.  Define it as char(8), then create rules to enforce valid date
structure (eg. pos 1-4  (0-9), pos 5 (0-1) [for months] pos 6 (0-9) pos 
7
(0-3) day and pos 8 (0-9) next create a couple of UDF's for any date math yo
u
might need.  This might seem like a lot of work, but it works great !!!  (we
ported from DB2 to SQL) and I avoided the need to make application changes b
y
doing this.



"Danicaban" wrote:

> Hello: i´m developing an application where must be loaded a lot of histor
ical
> data, including birth dates before 1753.
> I´ve been searching the web and find suggestions like "it´s an input
> mistake" or "take dates as string", but i really need to use those fields
> like dates.
> Can you say me if there is another solution, or tell me why is this
> restriction applied in SQL SERVER ? What is the benefit of this restrictio
n ?
> Thanks.

[ Post a follow-up to this message ]