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

[Jim]

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

-Jim

[David Portas]

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

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. 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@mot orola.com> wrote in message
news:729757f9.0 311251211.3d874 d62@posting.goo gle.com...[color=blue]
> Whats the format to send to a datetime field to distinguish it as B.C. vs[/color]
A.D.[color=blue]
>
> -Jim[/color]

[Greg D. Moore \(Strider\)]

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


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:KqydnbIRyL zeIF6iRVn-vg@giganews.com ...[color=blue]
> 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[/color]
year[color=blue]
> is very problematic because of the different and conflicting calendars in
> use in different parts of the world.[/color]

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

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

[color=blue]
>You could create a calendar table
> containing all the dates which are valid in the calendar you want to use[/color]
and[color=blue]
> 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@mot orola.com> wrote in message
> news:729757f9.0 311251211.3d874 d62@posting.goo gle.com...[color=green]
> > Whats the format to send to a datetime field to distinguish it as B.C.[/color][/color]
vs[color=blue]
> A.D.[color=green]
> >
> > -Jim[/color]
>
>[/color]