November 5, 2010 at 8:48 am
SanDroid (11/5/2010)
Hugo Kornelis (11/5/2010)
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.Hugo, is the default date format a server level default or user level regional setting?
Never mind Hugo, I found my answer in the following BOL documentation:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/3b078d5b-526c-4884-906a-e4442805795f.htm
String literal formats affect the presentation of data in applications to users but not the underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.
Some string literal formats are not affected by these settings.
To find out the default string literal format for down-level clients, see the topic for each date and time data type.
According to the BOL, the down-level client regional settings truely determine the String literal date format interpetation and presentation layer settings. SQL server and the SQL Native Client just use a chart or formula to select the best option depending on the down-level client settings.
November 5, 2010 at 8:56 am
honza.mf (11/5/2010)
If you don't specify any explicit language or dateformat, server default is used.
What TSQL comand shows the Sever Default dateformat for a MS SQL server 2005 or 2008? 😎
November 5, 2010 at 9:33 am
Hugo Kornelis (11/5/2010)
Steve Eckhart (11/5/2010)
I also didn't want to just run the code to get the question, so I converted July 1, 2010 and January 1, 2010 to integers in Excel. Excel calls these values 40185 and 40360, so I chose 0. I learned something from the question: Excel and SQL Server uses different 0 dates.Hi Steve,
You posted this while I was writing my previous reply.
Though many people describe the internal date format of Excel as "number of days since Dec 31, 1899", the more accurate (and also more painful for the Excel dev team) description is "number of days since Jan 1, 1900, if you count 1900 as a leap year".
Zero based index/offset counting strikes again. 😉
Next question, how many days in the year 0 AD?
November 5, 2010 at 9:45 am
Bazzkar,
With so many comments on your QOD, you really should be happy about it. Despite it perhaps causing some transitory embarrassment, you've engendered a lively discussion. Even though I was the first to point out that the configurable date format could affect the result, I've still learned a lot from the other posts.
In other words, as seems to be proven over and over, some of the best learning in the QOD feature at SSC comes from the discussion of the question and explanation. Please do submit more entries (after perhaps a bit more research and consideration).
November 5, 2010 at 9:58 am
While I fall under US jurisdiction. I try to keep an international awareness. I prefer the ymd format for very selfish and pragmatic reasons outside of SQL, file names and sorting. It is nice to know if I slip I've many people on the other side of the pond to help me remember.
I took it for granted that the number was supposed to be the date 1 July 2010. Perhaps it should have been noted in a comment. Would it be an unfair expectation that all questions should be free of syntactical errors unless the question is specifically asking how many errors are in the phrase:
Set Language English
Selekt sisobjects.name FR0M sysobjects
Of course as I say this my daughters pre-algebra book had an expression that when simplified yielded:
-6=4
Tim
November 5, 2010 at 10:00 am
SanDroid (11/5/2010)
Hugo, is the default date format a server level default or user level regional setting?
Simple question, but with a complicated answer. And I'll start with a disclaimer - I am not 100% that everything I write here is actually correct.
You can always explicitly override any defaults using SET LANGUAGE. If you do that, this settings takes precedence over anything else.
With no SET LANGUAGE, SQL Server will use the default language associated with the login. You can set and/or change this with the DEFAULT_LANGUAGE keyword of CREATE LOGIN and ALTER LOGIN.
If you don't specify a default language for a new login, SQL Server will set the default language equal to the server default language. The server default language is determined during setup. As far as I know, this is not configurable - setup determines a language and that's what you get. (If it is configurable, I must have missed the screen where you can enter it). I don't know how the language is determined. I thnik I once heard that this is determined by the language of the OS, or by the Windows locale settings in use when installing - but I always use Dutch OS versions, I have set my Windows locale settings to Dutch, yet all my SQL Server instances have English as the server-level default language. This suggests that the server language is determined by the version of SQL Server you downloaded -there is no Dutch version, so I downloaded and installed the English version, but a German DBA would probably buy the German version, which does exist.
November 5, 2010 at 10:50 am
Thanks for the question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 5, 2010 at 11:46 am
timothy bates (11/5/2010)
Zero based index/offset counting strikes again. 😉
Next question, how many days in the year 0 AD?
This would be a very good question as the correct answer would be zero. I believe that AD means Anno Domini (in the Year of the Lord), meaning that the count begins from year 1 AD, the previous year is called 1 BC, so there was not a year 0 AD.
Oleg
November 5, 2010 at 2:14 pm
From Spain...In my default dateformat configuration (dmy) returns 1.
November 5, 2010 at 2:45 pm
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.
November 5, 2010 at 2:57 pm
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.
November 5, 2010 at 6:12 pm
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
November 7, 2010 at 3:59 pm
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.
November 8, 2010 at 9:29 am
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:
November 8, 2010 at 10:08 am
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
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply