Earlier date than 1/1/1753 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Earlier date than 1/1/1753

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-08-26 : 04:15:45

I have an application running for over a year now and have recently run into a problem with datetime. The application is in the legal field and the users are running into difficulty in that the earliest date that can be entered into a datetime field is 01/01/1753.

Is there an easy workaround or am I looking at changing the data type to something else, char(10) for example, and coding accordingly?

Declan

-dw

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-26 : 04:21:31

Why do you want to insert those Old dates in DateTime field?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 04:32:23

you're looking at changing the datatype and coding accordingly....
or you can put the dates before 1753 in another table and save the date into 3 columns:
year, month, day.
if it is possible in your case of course.

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-08-26 : 04:38:52

madhivanan,
Old dates relate to title deeds on properties. The oldest I have come across so far is 1704.

spirit1,
As I suspected, I will need to change the data type. It was worth asking before I began, just in case there may have been a simple workaround.

Thanks to you both,
Declan

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 04:53:50

are you doing any calculations with those "old" dates?
because if you need them just for displaying you can add then say... 5000 years...
and save them in db as year 1704 + 5000 = 6704....
just an idea...

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-08-26 : 05:07:50

I am trying to establish how frequently this problem might occur. The dates are not used for any calculations so no difficult arises there. They are basically used for display/print so a change from datetime to char(10) might be the simplest solution. I just need to ensure I change all the appropriate SPs etc.

In the meantime the users can record such dates in a "notes" field that I have for the purposes fo handling anything not already covered by fields on the record. If dates prior to 1753 are few and far between then the "notes" field coould be the final solution to this problem. - "if it ain't really broke, don't fix it"

-dw

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-08-26 : 07:47:35

If not used in a calc, the note field could have a "standardised comment2 to indicate that 'this really old date "01/01/1753"...is actually an artificial system-low date...and that the real date for the title-deed is xx/yy/zz.

Andrew
(Dublin)

Kristen
Test

22859 Posts

Posted - 2005-08-26 : 09:03:42

"if you need them just for displaying you can add then say... 5000 years..."

Watch out for leap Years if you do that - the add-on probably needs to be a multiple of 400 years, I think, to have the leap dates repeat. Then there is days-of-the-week ...

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 09:07:24

I still find this "Accuracy" as M$ like to portray a serious joke.

They built a "business rule" into a RDBMS that has no business being there.

And since it is so arbitrary as to what the date should be (Many different countries had many different shift), it makes it more so.

Of course that's just me shooting from the hip, but I'm sure they picked the latest change in the epoch, not that China really cared.

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-26 : 09:10:04

brett:
what's with the "moo"??
you going into cattle business??

kristen:
that's why i said if you just need it for displaying...
then nobody cares about date of week and if it's a leap year

Go with the flow & have fun! Else fight the flow

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-08-26 : 09:11:29

An analysis on the occurrences of dates prior to 1753 shows very few. Based on this I will run with using the "notes" field.

Thanks for the quick replies,

-dw

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-26 : 09:15:41

just to chip in... we just talked about old dates here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54203

Ignore all of the fluff at the end

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 09:22:34

quote:
Originally posted by spirit1

brett:
what's with the "moo"??
you going into cattle business??

Well since Mr. Mist doesn't pop in that often anymore, I thought I'd MOO for both of us.

His MOO is of the bovine nature, mine are when I go on a rant and offer My Own Opinion

http://www.sharpened.net/glossary/acronyms.php#M

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-26 : 09:56:57

I think they picked that date because it was the first start of year after England and it’s colonies switched to the Gregorian calendar. I guess they had to pick a point, and since the US is their major market, they went with that.

Also, I am not sure if this was really a Microsoft decision. Perhaps this is a legacy from Sybase?

quote:
Originally posted by X002548

I still find this "Accuracy" as M$ like to portray a serious joke.

They built a "business rule" into a RDBMS that has no business being there.

And since it is so arbitrary as to what the date should be (Many different countries had many different shift), it makes it more so.

Of course that's just me shooting from the hip, but I'm sure they picked the latest change in the epoch, not that China really cared.

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 10:02:51

perhaps, but is there really any reason to propogate such a constraint.

I know of no other RDBMS (save sybase) that does this.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-26 : 10:10:38

How do other databases handle older dates? Do they have some other start point?

quote:
Originally posted by X002548

perhaps, but is there really any reason to propogate such a constraint.

I know of no other RDBMS (save sybase) that does this.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 10:29:11

quote:
Originally posted by Michael Valentine Jones

How do other databases handle older dates? Do they have some other start point?

quote:
Originally posted by X002548

perhaps, but is there really any reason to propogate such a constraint.

I know of no other RDBMS (save sybase) that does this.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

Yeah, 0000-00-00 - 9999-12-31

I guess you need another column to specify BC or AD

what's beyond 9999, I do not know.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-26 : 10:38:52

Do any of them have in built in support for switches from the Julian to the Gregorian calendar, or are you just on your own with that? For example, the day after 2 September 1752 being 14 September 1752 in England and the US.

quote:
Originally posted by X002548
Yeah, 0000-00-00 - 9999-12-31

I guess you need another column to specify BC or AD

what's beyond 9999, I do not know.

CODO ERGO SUM

liffey
Yak Posting Veteran

58 Posts

Posted - 2005-08-26 : 10:38:58

quote:

what's beyond 9999, I do not know.

I don't think I'll be around to worry about it. Will it be another "Y2K" project to earn someone a fortune

-dw

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-26 : 10:46:11

I remember people saying in 1980 that they wouldn't have to worry about the year 2000 problem.

Me? I'm getting ready for the year 10K problem right now.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-26 : 10:50:59

DB2 Doesn't care


SQL QUERY                                            MODIFIED  LINE    1   
                                                                           
                                                                           
--SET CURRENT SQLID = 'BXBB72DA';                                          
--CREATE TABLE MYTABLE99(COL1 DATE);                                       
--INSERT INTO MYTABLE99(COL1)                                              
--SELECT '1752-09-02' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-03' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-04' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-05' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-06' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-07' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-08' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-09' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-10' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-11' FROM SYSIBM.SYSDUMMY1 UNION ALL                      
--SELECT '1752-09-12' FROM SYSIBM.SYSDUMMY1;                               
                                                                           
SELECT * FROM MYTABLE99;                                                   
                                                                           
1=Help       2=Run        3=End         4=Print     5=Chart       6=Draw   
7=Backward   8=Forward    9=Form       10=Insert   11=Delete     12=Report

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources