B.C. vs A.D. datetime in SQL Server

Whats the format to send to a datetime field to distinguish it as B.C. vs A.D.

-JimSQL Server's DATETIME data type only permits dates in the range 1753-01-01
to 9999-12-31 (AD). If you need to record dates outside of this range then
you should use a CHAR or numeric column to do so.

Storing and manipulating ancient dates to a precision of more than one year
is very problematic because of the different and conflicting calendars in
use in different parts of the world. You could create a calendar table
containing all the dates which are valid in the calendar you want to use and
then reference that from your table. Maybe:

CREATE TABLE Calendar (era CHAR(2) CHECK (era IN ('BC','AD')), caldate
CHAR(8), PRIMARY KEY (era,caldate))

CREATE TABLE Sometable (... eracol CHAR(2) NOT NULL, datecol CHAR(8) NOT
NULL, FOREIGN KEY (eracol, datecol) REFERENCES Calendar (era, caldate)...)

--
David Portas
----
Please reply only to the newsgroup
--

"Jim" <jim.ferris@.motorola.com> wrote in message
news:729757f9.0311251211.3d874d62@.posting.google.c om...
> Whats the format to send to a datetime field to distinguish it as B.C. vs
A.D.
> -Jim|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:KqydnbIRyLzeIF6iRVn-vg@.giganews.com...
> SQL Server's DATETIME data type only permits dates in the range 1753-01-01
> to 9999-12-31 (AD). If you need to record dates outside of this range then
> you should use a CHAR or numeric column to do so.
> Storing and manipulating ancient dates to a precision of more than one
year
> is very problematic because of the different and conflicting calendars in
> use in different parts of the world.

http://www.tondering.dk/claus/cal/calendar26.html

For all you wanted to know and were afraid to ask.

>You could create a calendar table
> containing all the dates which are valid in the calendar you want to use
and
> then reference that from your table. Maybe:
> CREATE TABLE Calendar (era CHAR(2) CHECK (era IN ('BC','AD')), caldate
> CHAR(8), PRIMARY KEY (era,caldate))
> CREATE TABLE Sometable (... eracol CHAR(2) NOT NULL, datecol CHAR(8) NOT
> NULL, FOREIGN KEY (eracol, datecol) REFERENCES Calendar (era, caldate)...)
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --
> "Jim" <jim.ferris@.motorola.com> wrote in message
> news:729757f9.0311251211.3d874d62@.posting.google.c om...
> > Whats the format to send to a datetime field to distinguish it as B.C.
vs
> A.D.
> > -Jim