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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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."
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -