Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Predict the total count Expand / Collapse
Author
Message
Posted Friday, November 5, 2010 2:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
OCTom (11/5/2010)
Hugo Kornelis (11/5/2010)
OCTom (11/5/2010)
Why would the two platforms differ by one day?

Probably because of February 29, 1900.

There is no such date. The full rule for leap years is not "evey four years", but "every four years, except every 100 years, except every 400 years". That is, a year is a leap year if it's divisible by 400, or if it's devisible by 4 but not by 100.

Some software companies blundered. For instance, very early versions of MS Excel thought Feb 29, 1900 existed. And since the internal storage of dates used the number of days since a base date, all versions since had no choice but to continue including this incorrect date. (Except if you choose the 1904-date system - in that case, you simply can't handle dates before Jan 1st, 1904 so it's not an issue anymore).

SQL Server uses the correct leap year rules. Feb 28th, 1900 corresponds to the integer 58, Mar 1st, 1900 to the integer 59, and ant attempt to operate on the date Feb 29th, 1900 results in an error.

So while I don't know anything about Infinium software or the AS/400, the one date difference you see suggests that this software has fallen victim to the same error that MS Excel has.


Thanks Hugo.



While this is mostly true, don't read into it too far.

For instance, you can cast -1 as a datetime. You'll get 1899-12-31.
This can go back to 1753-01-01, but not to 1752-12-31.





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1016793
Posted Friday, November 5, 2010 6:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 6,046, Visits: 8,325
mtassin (11/5/2010)
For instance, you can cast -1 as a datetime. You'll get 1899-12-31.
This can go back to 1753-01-01, but not to 1752-12-31.

And *that* is because of the change from the Julian calendar (introduced by Julias Ceasar in 45BC; one leap day every four years, with no exception, for a year length of exactly 365.25 days) to the Gregorian calendar (introduced by Pope Gregory XIII; one leap day every four years, no leap day every 100 years, except every 400 years, for a year length of exactly 365.2425 days). In order to correct the error that had build between 45BC and the moment the switch was made, several days had to be skipped. But the transition was made at different days in different countries.
For example, Spain adopted the new calendar in 1582. The error was 9 days by then, so they skipped from Julian Thursday, 4 October 1582 to Gregorian Thursday, 5 October 1583. Greece, on the other hand, waited until 1923 - the amount of days to correct was 13 by then, so they went from Wednesday, 15 February 1923 to Thursday, 1 March 1923.

Britain, the British Empire, and their possessions (a.o. the Eastern part of what now is the United States) switched in 1752 - Wednesday, 2 September 1752 was followed by Thursday, 14 September 1752. So it looks as if the choice to cut off the valid range for datetime at 1753 is inspired by a very Anglosaxan-centric idea of how to prevent extra complexity to this data type.

See http://en.wikipedia.org/wiki/Gregorian_calendar for much more useless information on this fascinating subject

Edit: fixed the hyperlink



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1016840
Posted Sunday, November 7, 2010 3:59 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
yes Different region will have different answers but when no regions mentioned we normally take US as a default region specially when we talk about dates.
Post #1016982
Posted Monday, November 8, 2010 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:43 PM
Points: 216, Visits: 119
Somebody has their dates wrong, and if my wife has anything to say it would be me.

With that in mind the folks over at http://www.searchforancestors.com/utility/gregorian.html say that Greece adopted the Gregorian calendar in 1916. Wikipedia doesn't list the exact dates just the year. So I had been half tempted to submit a QOTD in jest.

Set Language 'GREECE'

--The format for dates used here is YYYYMMDD and is intended to be functional within your environment.

CREATE TABLE [dbo].[Gregory] (
[dt] [datetime] NOT NULL ,
[row] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO Gregory VALUES ('19160821',1)
INSERT INTO Gregory VALUES ('19160921',1)
INSERT INTO Gregory VALUES ('19161021',1)

Select count * from Gregory

Should I get an error or count 2 instead of 3?
Post #1017266
Posted Monday, November 8, 2010 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,801, Visits: 9,551
Hugo Kornelis (11/5/2010)
Terrible question. The only correct answer is "it depends", and that answer was missing.

If the default language of your server happens to be, for example, British English, you'll get 1 as your answer. Same for German, Dutch, French, and several other languages.

Add this to the fact that it's impossible to know what date the number 40358 represents, and you get a question that is impossible to answer without running the code, and even after running it leaves you with a choice between two answers.

(I used an educated guess - in my experience, over 90% of the code that does not cater for international use comes from the US, so I picked the answer I got when using SET LANGUAGE us_english)

Please do submit more questions in the future - but not of this quality.


Totally agree, except I suspect your 90% is an underestimate. I'm sure that if you look at developers who think it is better to have stuff which only works in one culture because it's less coding effort you will find that almost 99% are Americans. (Probably it's still a low proportion of Americans, but nothing like as low as it is in other countries.)


Tom
Post #1017292
Posted Monday, November 8, 2010 10:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 1,676, Visits: 1,758
timothy bates (11/8/2010)

Set Language 'GREECE'

--The format for dates used here is YYYYMMDD and is intended to be functional within your environment.

CREATE TABLE [dbo].[Gregory] (
[dt] [datetime] NOT NULL ,
[row] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO Gregory VALUES ('19160821',1)
INSERT INTO Gregory VALUES ('19160921',1)
INSERT INTO Gregory VALUES ('19161021',1)

Select count * from Gregory

Should I get an error or count 2 instead of 3?

You should get 2 errors: the language should be Greek, not Greece, and the count * should be count(*) or even better count(1). Other than that, when you opt to use YYYYMMDD format, it is independent on local settings, as it is one of the 2 universally accepted ISO formats, meaning that once your syntax is corrected, you should get 3 records and no errors

As far as switching to the Gregorian from Julian, while Greece accepted to change their calendar, Orthodox Christian Church did not, and therefore, all Holidays are still off by 13 days. For example, in Greece, Russia, Lebanon and other countries where main Christian denomination is Orthodox, Christmas Day is observed on 25th of December by Julian Calendar, which happens to be 7th of January. Additionally, in countries like Russia they observe an extra "New Year Day", which comes a week after Christmas (New Year eve is on 13 of January). Since in Russia there are Christians of other denominations besides Orthodox, they get to observe Christmas Day twice and New Year Day twice (all 4 days are official holidays, the offices are closed)

Oleg
Post #1017293
Posted Tuesday, November 9, 2010 12:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 6,046, Visits: 8,325
timothy bates (11/8/2010)
Should I get an error or count 2 instead of 3?

Allthough not explicitly documented, the SQL Server data type supports the Gregorian calendar. The SET LANGUAGE affects the default date format, but does not change the calendar. Though the Greeks do not have September 21, 1916 in their national calendar, Gregorian September 21, 1916 does exist even in Greece (and equates to September 8, 1916 in the Greece national calendar).

If SQL Server would observe the national Greece calendar, it should also support the Bahá'í, Buddhist, Chinese, Eastern Christian, Ethiopian, Hebrew, Hindu, Indian, Iranian, Islamic, and Thai Solar calendars (*hopes he didn't miss a calendar*)

And based on the article you linked to, SET LANGUAGE Dutch should result in a follow-up question to determine the exact province...


Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)

Why would that be better?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1017645
Posted Tuesday, November 9, 2010 1:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 1:20 AM
Points: 1,387, Visits: 1,315
Hugo Kornelis (11/9/2010)

If SQL Server would observe the national Greece calendar, it should also support the Bahá'í, Buddhist, Chinese, Eastern Christian, Ethiopian, Hebrew, Hindu, Indian, Iranian, Islamic, and Thai Solar calendars (*hopes he didn't miss a calendar*)


Don't forget Mayan calendar! Some two years to go, and ...


And the discussion about eastern calendars remembered me a joke:
Lived Lenin? Yes, but his true name was Ulyanov.
Lived Stalin? Yes, but his true name was Djugashvili.
Was a Great October Socialist Revolution? Yes, but it happened in November.
And it is all the same.




See, understand, learn, try, use efficient
© Dr.Plch
Post #1017654
Posted Tuesday, November 9, 2010 1:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 2,562, Visits: 2,420
Don't forget Mayan calendar! Some two years to go, and ...


I agree!
Post #1017657
Posted Tuesday, November 9, 2010 3:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 4:43 AM
Points: 1,130, Visits: 1,391
Question is average but conversation about the topic & date format is great. Thanks

Thanks
Post #1017723
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse