SQL Clone
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.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4804 Visits: 72518
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11000 Visits: 11992
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 (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 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 (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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 (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14302 Visits: 12197
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.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: 1777 Visits: 1814
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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11000 Visits: 11992
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.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

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

I agree! :-D:-D:-D

I run on tuttopodismo
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1582 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