Datetime Default

  • Koen Verbeeck (11/27/2012)

    Hugo Kornelis (11/27/2012)

    Koen Verbeeck (11/26/2012)

    Nice question. The explanation could be a bit more clear.

    That is a very polite way to put it.

    That's the difference between the Belgians and the Dutch 😉

    :laugh: I'll drink to that! Proost!

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

  • seankyleprice (11/27/2012)

    Given that the nullability of the column wasn't explicitly specified (a personal bugbear of mine) surely the answer depends on the setting for 'ANSI null default' on tempdb and whether or not ANSI_NULL_DFLT_ON is set for the connection?


    My first thought, since I always try to explicitly CREATE TABLE with either 'NULL' or 'NOT NULL', was that this would throw an error when trying to insert a NULL value, as the table declaration didn't explicitly state that the column could be NULL. As Sean states, if your session had first been set up as follows:


    then the INSERT statement in the question fails.


  • Glad to see I'm not the only one perplexed by the explanation. I was starting to wonder if something was really wrong with SQL 2008 and it slipped right by me.

    Aigle de Guerre!

  • Tricky and messy question. Altough I answered it right, I feel a little like I was wrong.

  • Malli,

    First of all, thank you for submitting a QOD. Please don't be discouraged by the barrage of comments, but rather accept that by posting one of these, your words will be examined and parsed to the n-th degree. You may want to take this as a lesson that would apply to your work in general; a first assumption should probably NEVER be put forth as a definitive answer.

    For example, "The database crashed because program X had a coding error." may be a disasterous thing to say if you don't absolutely know it's true. If it's not, you've left the real cause of the problem to possibly recur and you've also undercut your reputation with management and reduced your chances of cooperation from developers.

    Yes, this was just a silly little quiz question, but (as you've now seen), erroneous statements presented as facts can blow up quite a storm.

  • I second the opinion that the question is nice but the explanation is confusing. In any case, thanks for the QotD!

  • Koen Verbeeck (11/26/2012)

    Nice question. The explanation could be a bit more clear.

    It's not the default from the default expression GETDATE(), but the default value that is chosen when 0 or space is converted to a datetime.


    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • "Assume that getdate() is today's date"

    No assumption required!

    I think you meant to say:

    Assume that the query is run at precisely 19/10/2012 12:40.

    I have to echo john.arnott and say don't be discouraged, I think it's a bit of a tradition here to rip explanations to shreds when they aren't right. DBAs are a pedantic lot, with good reason of course! (me included)

  • nice question, poor and bad explanation alas

Viewing 9 posts - 16 through 23 (of 23 total)

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