November 5, 2010 at 3:41 am
Hugo Kornelis (11/5/2010)
(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)
I used the same tactics. If somone don't take care of other languages, he is often English speaking, and USA is a good quess.
November 5, 2010 at 3:45 am
I knew when I saw it that this question was going to rely on a particular date format being the default...surely that sort of thing should be checked before they're posted?
November 5, 2010 at 3:47 am
So when the conversion into floats and then taking ceiling of the values, these 2 records generate the same values.
I disagree with explanation, it's enough to convert to INT
Select count(intField) from TableDate where convert(INT,datefield) = 40358
November 5, 2010 at 3:53 am
Most of complainers: The problem is not dd/mm/yyyy against mm/dd/yyyy as both combinations are inserted (First of July and Seventh of January).
Problem is in yyyy/dd/mm against yyyy/mm/dd. And both of them are valid.
Why convert datetime to float? I don't know. Because it is permitted?
November 5, 2010 at 6:36 am
This is an odd question. I instantly recognized 40358 as the "hundred-year format" date used by Infinium software on the AS400. I ran 40358 against the converter and returned 6/30/2010. Why would the two platforms differ by one day?
I agree with others that 40358 is odd to have in the question and why would anyone use it instead of a real date if they don't have to. I'm forced to deal with it in the Infinium software but I wouldn't choose to.
November 5, 2010 at 6:51 am
For a US centric guy, this is an interesting discussion. Thanks.
November 5, 2010 at 7:02 am
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.
November 5, 2010 at 7:06 am
honza.mf (11/5/2010)
Most of complainers: The problem is not dd/mm/yyyy against mm/dd/yyyy as both combinations are inserted (First of July and Seventh of January).Problem is in yyyy/dd/mm against yyyy/mm/dd. And both of them are valid.
Yes, they are all valid dates, but the results will be different depending on what got inserted into the database because the 40358 means the SELECT is looking for a specific date. If the question's answer depends on what date format you're using, and the question does not specify what date format to use, then it's a bad question to start with--quite apart from the fact you wouldn't possibly know which of the inserted dates the 40358 referred to without actually running a script!
November 5, 2010 at 7:07 am
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.
November 5, 2010 at 7:09 am
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".
November 5, 2010 at 7:16 am
Hugo,
Excellent explanation. Thank you!
Steve
November 5, 2010 at 8:10 am
Steve Eckhart (11/5/2010)
Excel and SQL Server uses different 0 dates.
For whatever reason, the crrent version of EXCEL and SQL use differant default formats for storing all data types.
IMO: it has to do with with MS changing to an XML document format.
Not an obscure C compiler bug from the 80's
November 5, 2010 at 8:10 am
Bob Cullen-434885 (11/5/2010)
The question asks us to 'predict' the outcome, which implies you do it by inspection. But how is anyone supposed to know that 40358 is a relevant date serial?Poorly constructed question, I'd say.
That was my primary objection to this... the only way to "predict" it would be to start figuring the number of days elapsed since 01/01/1900.
Not something I normally can do. Whcih means I have to construct enough sql to figure out what the day portion of those fields is.
The fact that DATEFORMAT can be different and 01/07/2010 and 07/01/2010 could be the same date without it, is a side bonus of complaints.
November 5, 2010 at 8:33 am
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?
If I install the "German" language version of Windows server and SQL server in the US, select a US Time zone, and US region will I still get dates in SQL server as dd/mm/yyyy?
I have set up systems that needed differant regional date formats than US.
I had to log on as the SQL service account and set the desired format in the regional settings before the dates went in and out of the SQL server to other programs on systems running the non US date format as expected.
Of course this was not a Foreign language version installs of MS server or SQL server.
How does it realy work with those?
November 5, 2010 at 8:47 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?
If I install the "German" language version of Windows server and SQL server in the US, select a US Time zone, and US region will I still get dates in SQL server as dd/mm/yyyy?
I have set up systems that needed differant regional date formats than US.
I had to log on as the SQL service account and set the desired format in the regional settings before the dates went in and out of the SQL server to other programs on systems running the non US date format as expected.
Of course this was not a Foreign language version installs of MS server or SQL server.
How does it realy work with those?
This one is a session property. You can set language explicitly for each session.
If you don't specify any explicit language or dateformat, server default is used.
In our application, I have made a configuration option to select preffered language. I set it myself to German for debugging purposes. After some time I was surprised why our server speaks German 🙂
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply