Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Predict the total count


Predict the total count

Author
Message
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4109 Visits: 72512
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
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
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
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 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.
timothy bates
timothy bates
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 124
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? Hehe
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
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

Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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? Hehe

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) :-D

Oleg
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8329 Visits: 11580
timothy bates (11/8/2010)
Should I get an error or count 2 instead of 3? Hehe

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... w00t


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
honza.mf
honza.mf
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1771 Visits: 1323
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 ...
:-D

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
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 3236
Don't forget Mayan calendar! Some two years to go, and ...
:-D

I agree! :-D:-D:-D
Hardy21
Hardy21
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 Visits: 1399
Question is average but conversation about the topic & date format is great. Thanks

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search