• Comments posted to this topic are about the item smalldatetime

    [font="Comic Sans MS"]Rahul:-P[/font]

  • Nice question.


  • Not so sure about the explanation given--smalldatetime doesn't have a "default value" of 1 Jan 1900, it's just that the underlying number that stores the date uses 0 to represent that date.

  • Good (if fairly easy) question, but bad explanation.

    The default for smalldatetime is not Jan 1, 1900; it is NULL. Here is the proof:

    DECLARE @d1 smalldatetime;

    SELECT @d1;

    Also, setting a variable to 0 is not at all requesting to set it to the default, it is requesting to set ot to the value 0, or whatever is the result of implicitly converting 0 to the data type of the variable.

    DECLARE @v0 float, @v1 char(20), @v2 varbinary(max), @v3 xml, @v4 uniqueidentifier;

    -- Show default values - NULL for all data types

    SELECT @v0, @v1, @v2, @v3, @v4;

    -- Set to result of implicit conversion of 0 to specified data type

    SET @v0 = 0;

    SET @v1 = 0;

    SET @v2 = 0;

    -- Uncomment to get conversion errors

    --SET @v3 = 0;

    --SET @v4 = 0;

    -- Show results

    SELECT @v0, @v1, @v2, @v3, @v4;

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

  • Well that's what I get for second-guessing myself. It seemed like such an easy question, the obvious answer couldn't possibly have been right. Guess that'll teach me! 😉


    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Great question.

    I didn't know it beforehand, but it sure made sense when I thought the question over.

  • Nice, easy question. And thanks to Hugo for the extra, thorough explanation.

    Need an answer? No, you need a question
    My blog at
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Whenever we use any data type we should know it's default value

    Although I will agree that knowing these things has value, I am more of the opinion of never leave anything to default, declare everything. This way the default becomes irrelevant and you are much less likely to get a surprise in your results.

  • Thanks for the question and thanks to Hugo for the explanation.

    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

  • When a variable is declared in SQL by default it's value will be NULL, there is no default value concept in SQL variable declaration.



  • Thanks for the question, and thanks Hugo for your explanation.

    This question was really easy, as I knew the errors couldn't happen as they were stated, so there was only one option left.

  • Nice question, terrible explanation. SQL doesn't even have a concept of default values for its various datatypes.


  • "Whenever we use any data type we should know it's default value"

    I disagree (even ignoring the valid but slightly pedantic points about datatypes not having defaults).

    We should never rely on default values in this way, it is prone to error, and hard to read the code.

    If you want to assign a value to a date variable, then explicitly assign the value you want, don't give it a value of a different datatype and rely on implict conversion 🙂

  • Thanks for the question.

    The default value for the @date1 is NULL.

    Please see below proof. If I am commenting the 'set statement, I am getting @date1 as NULL.

    declare @date1 smalldatetime

    --set @date1 = 0

    print Isnull(@date1,getdate())


    Reji P R



    Reji PR,

  • @rejipr1982: Hugo already mentioned this in his response. Still thanks to evaluate the same.


Viewing 15 posts - 1 through 15 (of 15 total)

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