October 27, 2010 at 7:34 am
Fortuntely I was fibbing and we actually convert to proper dates before passing to SQLServer 🙂
October 27, 2010 at 7:45 am
SanDroid (10/27/2010)
I give this question Five Stars!It was code correct, the question was clear and had no typo's, and the author clearly understood the concept his question was testing.
This Question of the Day has been plagued lately with poorly worded and misspelled code examples and questions. That along with the fact they they were complex questions with complex SQL Server features and statements made them horible.
All I am reading is: easy question = good, difficult question = bad 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2010 at 8:27 am
DougieCow (10/27/2010)
I'm absolutely gob smacked somebody answered 29Apr, or even worse 31Mar. :ermm:
I'm kind of baffled anyone would have picked 29-Apr too--there's actually some logic to picking 31-Mar, but 29th April? :blink:
October 27, 2010 at 8:50 am
da-zero (10/27/2010)
All I am reading is: easy question = good, difficult question = bad 😉
I can tell by your reading and writing level you might be the moderator checking the questions before posting them.
Suggestion, actually try to execute the code and check for errors. This is the first QOTD I have looked at this month that didn't have any.
Is that easy or difficult for you to understand? 😎
October 27, 2010 at 9:12 am
SanDroid (10/27/2010)
da-zero (10/27/2010)
All I am reading is: easy question = good, difficult question = bad 😉I can tell by your reading and writing level you might be the moderator checking the questions before posting them.
Suggestion, actually try to execute the code and check for errors. This is the first QOTD I have looked at this month that didn't have any.
Is that easy or difficult for you to understand? 😎
The only people who review the questions are the question author (Abhijit for this question), and Steve Jones. And Steve often lacks the time to properly check the questions. So your assessment of da-zero is wrong.
I do not agree with the comment da-zero made to your post. But I also disagree with your enthousiasm about this question. I do agree partly with the second sentence you wrote ("It was code correct, the question was clear and had no typo's, and the author clearly understood the concept his question was testing"), though the code was definitely not safe in non-US environments.
But I totally disagree with your final statement ("That along with the fact they they were complex questions with complex SQL Server features and statements made them horible"). These questions should provide a learning opportunity, and that will hardly happen with questions as simple as this one. I expect the QotD to be about complex and/or little-known features.
(And yes, I also expect them to be error-free -which unfortunately happens less often than I would like).
October 27, 2010 at 9:46 am
Hugo Kornelis (10/27/2010)
SanDroid (10/27/2010)
da-zero (10/27/2010)
These questions should provide a learning opportunity, and that will hardly happen with questions as simple as this one. I expect the QotD to be about complex and/or little-known features.
I also expect the QOTD to challenge me and be about complex or little known features. However if the person submitting the questions does not understand them enought to check his code, or his question and answers for errors then what was the challange? My ability as a SQL DBA and Developer or my ability to read minds and decypher the "True meaning" of the question.
The Author of this question clearly understood what he was posting and took the time to make sure his question was well worded and that the code worked on a DEFAULT INSTALLATION OF MS SQL SERVER.
IMHO, all code samples I have ever worked with were written for a DEFAULT INSTALLATION OF MS SQL SERVER unless otherwise stated. I believe the same rules apply to the QOTD.
So with that being said, why do you think this code was unsafe?
Perhaps the question was more complcated than you give it credit for.
Apparently you tried to make it more complicated than necessary.
What do you call it when someone overcomplicates something that should be easy? :w00t:
October 27, 2010 at 9:53 am
Toreador (10/27/2010)
Hugo Kornelis (10/27/2010)
For the datetime data type, The only formats that are guaranteed to work correctly in all circumstances are:* yyyymmdd - for dates without time portion. No dashes. dots, slashes or other punctuation. The resulting datetime value will have its time portion set to midnight.
We use yyyy-mm-dd which has always worked so far.
Should I be worried?!
yyyy-mm-dd is not universal.
print 'Format mdy'
set dateformat mdy
select x1 =convert(datetime,'2010-12-31')
go
print 'Format ymd'
set dateformat ydm
select x2=convert(datetime,'2010-12-31')
Results:
Format mdy
x1
------------------------
2010-12-31 00:00:00.000
(1 row(s) affected)
Format ymd
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
October 27, 2010 at 10:09 am
Michael Valentine Jones (10/27/2010)
Toreador (10/27/2010)
Hugo Kornelis (10/27/2010)
print 'Format mdy'set dateformat mdy
select x1 =convert(datetime,'2010-12-31')
go
print 'Format ymd'
set dateformat ydm
select x2=convert(datetime,'2010-12-31')
Results:
Format mdyx1
------------------------
2010-12-31 00:00:00.000
(1 row(s) affected)
Format ymd
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Hugo,
What if the client application is submitting the data as XML and thier regional setting is English (Cannada) or some other setting not the same as the SQL server? Wouldn't that also change the code needed to safely convert the string to a valid DateTime data type?
October 27, 2010 at 10:47 am
QOD Part 2: Is January 31st, 2010 (the original date), the result of:
declare@StartDateTimedatetime;
SET@StartDateTime= CAST('2010-01-31T00:00:00.000' AS datetime)
SELECTDATEADD(MONTH, -3 , DATEADD(MONTH, 3, @StartDateTime))
SQL = Scarcely Qualifies as a Language
October 27, 2010 at 11:29 am
SanDroid (10/27/2010)
The Author of this question clearly understood what he was posting and took the time to make sure his question was well worded and that the code worked on a DEFAULT INSTALLATION OF MS SQL SERVER.
He did indeed understand what he was posting and he did take the time to ensure a well worded question; I won't deny any of that. But that's where our agreement ends.
There is no such thing as "a" default installation of MS SQL Server. What defaults are presented depends on a lot of things. One of them (the one that happens te be of importance for this discussion) is the Operating System language.
If you buy a computer in Germany, it will come preconfigured with a German version of Windows. And even if you buy a computer without OS, the operating system installed by a typical German user or at a typical German company will be German.
And if you ever install SQL Server on a German OS, acccepting all defaults, you'll find that the Server language also defaults to German.
I do not think this impacted the question. I would have if the date had been ambiguous to human readers (dates like "03-01-2010" are ambiguous, as I don't know if that's January 3rd of March 1st). Everyone who comes here knows enough English to understand what 31-Jan-2010 is, even if their installation of SQL Server doesn't. So I felt absolutely no reason to criticse the question because of this, and I haven't. But I did provide answers when people asked about the date format (for I am here to share knowledge as well as to learn), and I did object when you said that this question "didn't have any" errors, as that is not true.
IMHO, all code samples I have ever worked with were written for a DEFAULT INSTALLATION OF MS SQL SERVER unless otherwise stated. I believe the same rules apply to the QOTD.
I believe that for the QotD, as for any code published anmywhere, the rule is to use locale-independent date formats, unless explicitly mentioned otherwise.
October 27, 2010 at 11:31 am
SanDroid (10/27/2010)
Hugo,What if the client application is submitting the data as XML and thier regional setting is English (Cannada) or some other setting not the same as the SQL server? Wouldn't that also change the code needed to safely convert the string to a valid DateTime data type?
I'm sorry, but I have next to no knowledge of XML, and absolutely no experience with XML. I can't answer this question. Someone else, perhaps?
October 27, 2010 at 12:02 pm
SanDroid (10/27/2010)
da-zero (10/27/2010)
All I am reading is: easy question = good, difficult question = bad 😉I can tell by your reading and writing level you might be the moderator checking the questions before posting them.
Suggestion, actually try to execute the code and check for errors. This is the first QOTD I have looked at this month that didn't have any.
Is that easy or difficult for you to understand? 😎
Have smileys totally lost their value nowadays?
ps: my grammar/reading is perfectly acceptable for someone with English as a 3rd language.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 27, 2010 at 12:17 pm
da-zero (10/27/2010)
Have smileys totally lost their value nowadays?
I am not certain. Are they? :w00t:
I just used your response as a jumping off point. 😛
I know you are not the one moderating the Question of the Day.:Wow:
I did not mean it to be personal and thought you would realize that. :sick:
My point was/is nobody realy moderates the QOTD, and that is taking focus away from good questions that promote learning and placing it on bad spelling and gramer.
Of course if it was not for all these Forum posts, how many people would have a status above rookie on this site?
BTW: What was the intermediary language between you native language and English as the 3rd language? Was it C++, COBOL, QBASIC, SQL? 😎
October 27, 2010 at 1:10 pm
spelling and gramer
😛
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
October 27, 2010 at 1:17 pm
Hugo Kornelis (10/27/2010)
And if you ever install SQL Server on a German OS, acccepting all defaults, you'll find that the Server language also defaults to German..
Is that becuase of the Laguage of the OS, or the installing users regional settings in Control Panel?
When you responded to my inital post saying you disagreed that this was a good question and stated that the code was unsafe.
What was that supposed to make me or the author think?
Ich zweifele, er dachte, dass es ein Kompliment war.
I felt it only fair to point that out. If you are fair you should accept it and own it. :w00t:
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply