Data conversion

Question

Data conversion

Viewing 15 posts - 16 through 30 (of 101 total)

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

paul.jones SSC Eights! Points: 824 More actions · Answer 1

I was so convinced the extra comma in the CREATE TABLE would cause an error, I never even noticed the date of the 2nd of Chequary (Chequary is the month after December where you still write cheques with the wrong year on).

So, why is it OK to have the extra comma? In BOL http://msdn.microsoft.com/en-us/library/ms174979.aspx doesn't the [,...n] bit mean you can have a comma followed by another column definition, but not just a comma on its own? Why does having 2 commas at the end fail but one is OK?

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 2

Msg 8135, Level 16, State 0, Line 1

Table level constraint does not specify column list, table '#Money'.

Sql seems to think the first answer is correct.

OCTom SSChampion Points: 11785 More actions · Answer 3

I got the question correct because I didn't see the extra comma others have pointed out. Had I seen that, I probably would have thought the CREATE would fail.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

A lot going on in this question with the superfluous comma, reserved word usage and implicit conversions but interesting question, thanks.

M&M SSC-Insane Points: 21700 More actions · Answer 5

I don't prefer questions like this and the ones related to Visual Studio. 🙂

M&M

jcunningham Old Hand Points: 397 More actions · Answer 6

I see I am not the only one who noticed the extra comma. Did the author plan for this to throw people off??

Jaroslav Pfauser SSC Eights! Points: 826 More actions · Answer 7

Jaroslav Pfauser

SSC Eights!

Points: 826

March 18, 2011 at 7:55 am

#1300507

I have the same problem 🙂

tejaswini.patil SSCommitted Points: 1812 More actions · Answer 8

tejaswini.patil

SSCommitted

Points: 1812

March 18, 2011 at 7:56 am

#1300509

Even I had not noticed the extra comma.

SQLBalls SSCarpal Tunnel Points: 4621 More actions · Answer 9

Good question the extra "," almost threw me

Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field

Jaroslav Pfauser SSC Eights! Points: 826 More actions · Answer 10

Just Question.

If extra comma is allowed in create order

CREATE TABLE #TEST (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Date datetime,Amount INT,)

Why it is not allowed in SELECT

SELECT ID, DATE, Amount, FROM #TEST

?

comma in Create order have some special function or what?

Rich Mechaber SSChampion Points: 11130 More actions · Answer 11

Like others, I figured that Date was a reserved keyword, but then I thought "no", b/c the question said it would run on SQL 2005 and 2008. 2005 doesn't have a data type of DATE.

Curious, I checked BOL, and it turns out that in 2005, DATETIME isn't even a reserved keyword (http://msdn.microsoft.com/en-us/library/ms189822%28v=SQL.90%29.aspx). You can name that column DateTime and it runs just fine in 2005.

Nor are other data types listed as reserved keywords (try INT, for example: no problem). I'm surprised by this, and I guess I learned something today, too, although I would never name columns using data type words.

Thanks,

Rich

tejaswini.patil SSCommitted Points: 1812 More actions · Answer 12

I am curious about two issues:

1. Extra comma

2. Column name 'Date'

The extra comma should have given error.

Any ideas!!

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

When will people ever learn about date formats?

The correct answer ("depends on the locale settings") was not listed, so I had to throw a coin to choose between theory a (question submitted by a stupid American or Japanese who thinks the US/Jap system is the only system) or theory b (question submitted by a stupic non-American/non-Japanese who thinks THEIR system is the only system).

Note to anyone who ever intends to submit questions with hardcoded dates in them: please use the yyyymmdd format only! Or, if you need time as well, yyyy-mm-ddThh:mm:ss.

If you really want to tick people off by using a different date format, then FIRST run your code with a SET LANGUAGE statement in front of it, trying all the major languages - then consider if you still want to keep your code that way.

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/

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

mbova407 (3/18/2011)
Msg 8135, Level 16, State 0, Line 1
Table level constraint does not specify column list, table '#Money'.
Sql seems to think the first answer is correct.

I think you added a comma between the identity attribute and the primary key constraint for the ID column. Adding a comma there changes the semantics from a column level constrain to a table-level constraint.

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/

Surii Default port Points: 1497 More actions · Answer 15

Chris Houghton (3/18/2011)
I'm half asleep this morning. Missed every one of the potential gotchas (implict conversion, US date format and extra comma), thought "this code looks ok" and selected the right answer. If i'd been more awake I'd have got the answer wrong on several counts 😀

Same case with me..:w00t: