a sql datatime question..

according to sql 2k book online
Date and time data from January 1, 1753 through December 31, 9999
what if a couple of records in a huge data file actually have dates
backed to 1500 (and those are actual and valid publication dates), what
should i do? I want to be able to query the data by datetime, and don't
want to use varchar just for a couple of records, but at the same time,
i couldn't bring in the records with a record dated in 1500. is there a
work around?
thank you.=== Steve L === wrote:
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates),
> what should i do? I want to be able to query the data by datetime,
> and don't want to use varchar just for a couple of records, but at
> the same time, i couldn't bring in the records with a record dated in
> 1500. is there a work around?
> thank you.
Since you don't want to use a varchar column, how about encoding the dates
in this column by adding a couple thousand years to them before storing
them?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The data type datetime does not hold dates earlier than 1753 because of
the major changes that occurred in the calender system (in many
countries) in 1752. Because of that, IMO it would not be a good idea to
try to force it into a datetime column with some kind of workaround.
My advice would be to use a char(8) definition (a suggestion you already
rejected), use the format yyyymmdd when entering dates, and use a
constraint to enforce the format. For example:
CHECK ( MyDate LIKE Replicate('[0-9]',8) -- just digits
AND Floor(CAST(MyDate as int)/10000) BETWEEN 1000 AND 2099 --
year
AND Floor(CAST(MyDate as int)/100)%100 BETWEEN 1 AND 12 --
month
AND CAST(MyDate as int) %100 BETWEEN 1 AND 31 --
day
)
For dates >= 1753, you can safely cast these values to datetime. Also,
this format allows sorting on the column and range selections.
Hope this helps,
Gert-Jan
=== Steve L === wrote:
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates), what
> should i do? I want to be able to query the data by datetime, and don't
> want to use varchar just for a couple of records, but at the same time,
> i couldn't bring in the records with a record dated in 1500. is there a
> work around?
> thank you.|||thank you guys..
but that sucks.
i'm using the bulk insert to bring in library of congress data.(they
are huge)
i believe there are only a handful of publication dates are in 1950s
but that failed the process if i declare it as datetime datatype
(which is what i really want). i don't think i can do any
transformation in the bulk insert as suggested.|||Steve,
Basically you are out of luck here, but there is something you can do.
Create a temp table, bcp into it with the char date format and then massage
the date as the format you'd like to see while importing into a permanent
table. Possibilities include the ones mentioned in the previous replies on a
classic approach of having 3 integer fields for year, month and day.
Ilya
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107196701.961705.64680@.f14g2000cwb.googlegroups.com...
> according to sql 2k book online
> Date and time data from January 1, 1753 through December 31, 9999
> what if a couple of records in a huge data file actually have dates
> backed to 1500 (and those are actual and valid publication dates), what
> should i do? I want to be able to query the data by datetime, and don't
> want to use varchar just for a couple of records, but at the same time,
> i couldn't bring in the records with a record dated in 1500. is there a
> work around?
> thank you.
>