Post reply

Data conversion

  • March 18, 2011 at 4:54 am

    #1300406

    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?

  • March 18, 2011 at 6:24 am

    #1300440

    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.

  • March 18, 2011 at 6:52 am

    #1300466

    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.

  • March 18, 2011 at 7:47 am

    #1300500

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

  • March 18, 2011 at 7:51 am

    #1300503

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

    M&M

  • March 18, 2011 at 7:55 am

    #1300506

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

  • March 18, 2011 at 7:55 am

    #1300507

    I have the same problem 🙂

  • March 18, 2011 at 7:56 am

    #1300509

    Even I had not noticed the extra comma.

  • March 18, 2011 at 8:00 am

    #1300511

    Good question the extra "," almost threw me

    Twitter: @SQLBalls
    Blog: http://www.SQLBalls.com

  • March 18, 2011 at 8:01 am

    #1300514

    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?

  • March 18, 2011 at 8:02 am

    #1300515

    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

  • March 18, 2011 at 8:08 am

    #1300519

    I am curious about two issues:

    1. Extra comma

    2. Column name 'Date'

    The extra comma should have given error.

    Any ideas!!

  • March 18, 2011 at 8:08 am

    #1300520

    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/

  • March 18, 2011 at 8:10 am

    #1300521

    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/

  • March 18, 2011 at 8:12 am

    #1300522

    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:

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

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