SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Earlier date than 1/1/1753
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 08/26/2005 :  04:15:45  Show Profile  Reply with Quote
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

India
22754 Posts

Posted - 08/26/2005 :  04:21:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Why do you want to insert those Old dates in DateTime field?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/26/2005 :  04:32:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 08/26/2005 :  04:38:52  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/26/2005 :  04:53:50  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 08/26/2005 :  05:07:50  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 08/26/2005 :  07:47:35  Show Profile  Reply with Quote
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)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/26/2005 :  09:03:42  Show Profile  Reply with Quote
"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

Edited by - Kristen on 08/27/2005 01:57:35
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/26/2005 :  09:07:24  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/26/2005 :  09:10:04  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 08/26/2005 :  09:11:29  Show Profile  Reply with Quote
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
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/26/2005 :  09:15:41  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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."
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/26/2005 :  09:22:34  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2005 :  09:56:57  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/26/2005 :  10:02:51  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2005 :  10:10:38  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/26/2005 :  10:29:11  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2005 :  10:38:52  Show Profile  Reply with Quote
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
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 08/26/2005 :  10:38:58  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/26/2005 :  10:46:11  Show Profile  Reply with Quote
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/26/2005 :  10:50:59  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000