Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Date Puzzle Expand / Collapse
Author
Message
Posted Wednesday, October 27, 2010 9:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Hugo Kornelis (10/27/2010)
[quote]SanDroid (10/27/2010)
[quote]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?



Post #1011666
Posted Wednesday, October 27, 2010 9:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:15 PM
Points: 3,136, Visits: 11,492
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.


Post #1011678
Posted Wednesday, October 27, 2010 10:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Michael Valentine Jones (10/27/2010)
[quote]Toreador (10/27/2010)
[quote]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 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.




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?
Post #1011690
Posted Wednesday, October 27, 2010 10:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
QOD Part 2: Is January 31st, 2010 (the original date), the result of:
declare	@StartDateTime	datetime;
SET @StartDateTime = CAST('2010-01-31T00:00:00.000' AS datetime)
SELECT DATEADD(MONTH, -3 , DATEADD(MONTH, 3, @StartDateTime))



SQL = Scarcely Qualifies as a Language
Post #1011731
Posted Wednesday, October 27, 2010 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 6,002, Visits: 8,264
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011775
Posted Wednesday, October 27, 2010 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 6,002, Visits: 8,264
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?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1011777
Posted Wednesday, October 27, 2010 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,731, Visits: 10,689
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1011800
Posted Wednesday, October 27, 2010 12:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
da-zero (10/27/2010)

Have smileys totally lost their value nowadays?


I am not certain. Are they?
I just used your response as a jumping off point.
I know you are not the one moderating the Question of the Day.
I did not mean it to be personal and thought you would realize that.

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?

Post #1011805
Posted Wednesday, October 27, 2010 1:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:35 PM
Points: 831, Visits: 1,581
spelling and gramer





One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1011831
Posted Wednesday, October 27, 2010 1:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Hugo Kornelis (10/27/2010)
[quote][b]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.
Post #1011837
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse