473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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!
John
Jul 23 '05 #1
6 4238
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
--
Jul 23 '05 #2
David Portas (RE****************************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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.

Jul 23 '05 #4
Cheers for the help.
John

"David Portas" <RE****************************@acm.org> wrote in message
news:zq********************@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
--

Jul 23 '05 #5
Pedants' Calendar:
http://www.douglasadams.com/dna/pedants.html

;-)

--
David Portas
SQL Server MVP
--
Jul 23 '05 #6
David Portas (RE****************************@acm.org) writes:
Pedants' Calendar:
http://www.douglasadams.com/dna/pedants.html

;-)


Hehe, that was a good one!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: ruud habets | last post by:
while making an encyclopdie on my hometown (dutch, http://www.kgv.nl/index.php?id=610) i am trying to use dates prior to 1970. birthdates in, let's say, 1615. i'm getting errors while working...
8
by: netsurfer | last post by:
Hi: Have a question on making the date automatically filled in by what the user enters in by the date at the top. The date entered at the top would most likely be on a Wednesday then I need...
9
by: Rich | last post by:
Thanks for the Help in my previous post. I've been working on this and it's almost what I want. I want to obtain the user's current age by comparing their date of birth (user inputs) to the...
13
by: tshad | last post by:
I have a dataset with a date in it. The type is smalldatetime on Sql Server and I am just doing a "SELECT * from x" to get it. All my other fields are fine, but this one gives me an error of: ...
2
by: Child | last post by:
I am trying to insert some data into a table and keep gettting the error: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. Its me testing the database so...
14
by: Cesar Ronchese | last post by:
Hello! I've built a program that show some dates to users. I got a problem when users have different configured time zone machines, where: - One machine that determined time zone the date...
2
by: Jim Carlock | last post by:
(1) Does PHP provide any way to handle dates prior to 1980? I know there's problems with Microsoft Windows NT and all Windows NT operating systems will allow a date prior to 1980 to be placed...
1
by: favor08 | last post by:
have serval date fields in my table that i need to perform calcualations to bring bring a certain # of items. Fields: RptDte Date filed closed always the last date of previous month--- 6/30 for...
2
by: Zyronne | last post by:
Hello Experts. I have a database that deals with history. Since MSSQL 2000 cannot accept dates earlier than Jan 1, 1753 I converted my column to CHAR. My problem now is I cannot find dates on...
5
by: cla | last post by:
I'm using this code on an application to track football schedules: ---- $season = '2005'; $basedate = strtotime('this friday', strtotime('31 August '.$season)); for($d=0;$d<=31;$d++) {...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.