DATETIME 1

Question

DATETIME 1

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Allthough the intention of the question is valid and I got it correct, the SQL itself is not.

With the question being "What values are returned"

The correct answer is "None of the Above" or "Conversion failed when converting the varchar value ',' to data type int."

But I would thnk this is obvious. :hehe:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Ken SSCoach Points: 17191 More actions · Answer 2

Nice question. I hadn't used the iso-week datepart before.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

Jason Selburg (12/7/2011)
Allthough the intention of the question is valid and I got it correct, the SQL itself is not.
With the question being "What values are returned"
The correct answer is "None of the Above" or "Conversion failed when converting the varchar value ',' to data type int."
But I would thnk this is obvious. :hehe:

Actually, this is not true. If you check the SELECT case carefully, you'll see that there are commas between the expressions.

Each "+ ','" is an expression of itself, that evaluates to a varchar(1) expression with the value ','. So the result set will have seven columns, four for the actual data, interspersed with three columns with the value ',' in them.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Good question. The date format (mm-dd-yyyy) is irritating, but a fact of life, but obvious since a date in August isn't going to be week (whether WW or ISOWK) 2. And anyway, the correct answer could be deduced from the fact that Jan 1st 2010 was a Friday, as only one of the options begins with 53 and "none of the above" wasn't an option.

Pity about the explanation though; it copies a gross inaccuracy from the old SQLServerCentral article referenced. ISO week 1 does not contain "at least the first 4 days of the year", it contains the first 4 days of the year only when Jan 4th is a Thursday, Friday, Saturday, or Sunday. For example in 2005, ISO week 1 began on Monday Jan 3rd, so it included neither 1st Jan nor 2nd Jan.

In fact ISO week 1 sometimes contains none of the first three days of the year (any year in which 4th Jan is a Monday), in other years it contains neither of the first 2 days of the year (any year where 4th Jan is a Tuesday) and in yet other years it omits only the first day of the year (any year where 4th Jan is a Wednesday).

The correct definition of ISO week 1 is that it is the week (beginning Monday) that contains contains most (ie at least 4) of the first 7 days of the year. Alternatively - an equivalent definition, since ISO weeks begin on Mondays - it is the week (beginning Monday) containing both the first Thursday of the year and 4th Jan.

It might have been better to refer to the Bol page on DATEPART than to an article with that error in it.

edit: correct a few errors - this is more complicated than what I remembered!

Tom

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

Hugo Kornelis (12/7/2011)
Jason Selburg (12/7/2011)
Allthough the intention of the question is valid and I got it correct, the SQL itself is not.
With the question being "What values are returned"
The correct answer is "None of the Above" or "Conversion failed when converting the varchar value ',' to data type int."
But I would thnk this is obvious. :hehe:
Actually, this is not true. If you check the SELECT case carefully, you'll see that there are commas between the expressions.
Each "+ ','" is an expression of itself, that evaluates to a varchar(1) expression with the value ','. So the result set will have seven columns, four for the actual data, interspersed with three columns with the value ',' in them.

DOH! :hehe:

Well, The syntax is incorrect if you type it incorrectly and forget a comma like I did. LOL

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

thanks for the question. Not something that I have used before so learned something new today.

lee_uk SSC Rookie Points: 40 More actions · Answer 7

Yes, makes sense now I realise that it was a US date.

Stuart Davies SSCoach Points: 19505 More actions · Answer 8

lee_uk (12/8/2011)
Yes, makes sense now I realise that it was a US date.

Know what you mean - I fell foul of that today - oh well.

Only trouble is, if the criteria for the query, language settings, SQL version, what you had for breakfast that day etc, was specified, the set up for the question would be longer than the question itself.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx