Date data type

  • NicHopper

    SSCrazy Eights

    Points: 8875

    Comments posted to this topic are about the item Date data type

  • Oleg Netchaev

    SSCertifiable

    Points: 5272

    Good question, thank you. I answered correctly because I already tried it some time ago when I first learned about the new data type just to check whether behavior of date is the same with the one of datetime.

    Oleg

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Iam not using sql server 2008.

    so I learned one new thing today.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4222

    [font="Verdana"]Goodish, at least one of the requirements for QOD is fulfilled in this question statement, i.e to include the Version # of SQL.

    Although it was obligatory for this question but at least i appreciate this effort and will request other contributors to make question statements clear of ambiguities specially the Version Conflicts.

    Thanks !!!

    [/font]

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Good question. No points for me but I learned something new today.

    And also useful, when we will (finally) migrate to 2008.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • M@N@

    Valued Member

    Points: 52

    Hi to all,

    1.

    DECLARE @Today DATE

    it wii not work but if you change datetype "DATE" to "DATETIME"

    Then it will work

    DECLARE @Today DATETIME

    SELECT @Today = '01 DEC 2009'

    SELECT @Today+1

    SELECT @Today

    2.

    Directly we can't add integer value to date variable, by using DATEADD function we can add day(s)/year(s) to date variable.

    [font="Verdana"]Thanks & Regards
    M@N@[/font]

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Mostly a good question. I got it wrong, because I was too hasty - I overlooked that the data type was DATE, not DATETIME (probably because I still work with SQL2005 on a daily basis). I do know that adding an integer to DATE is not supported.

    What I dislike about the question is:

    * The date format. Not all locale settings recognise "Dec" as december. (Try adding "SET LANGUAGE German;" as the first line - at least on SQL Server 2005 and with the data type changed to datetime, that results in a conversion error)

    * The missing semicolons. In SQL Server 2008, not terminating statements with a semicolon is deprecated.

    * There were two SELECT statements, so the incorrect answer options should have included two results sets. (I guess the author intended the first SELECT to read "SELECT @Today = @Today + 1;" - yet another reason to prefer SET over SELECT).

    But the lesson learned is valuable. For me (to read carefully and think instead of just shooting from the hip), and for many others (that adding an integer to a date is not supported - just as it should never have been supported for datetime).

    Thanks for the question!


    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/

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    M@N@ (4/28/2010)


    Hi to all,

    DECLARE @Today DATE

    it wii not work but if you change datetype "DATE" to "DATETIME"

    Then it will work

    Why wouldn't that work? The date datatype exists on SQL Server 2008.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Niths

    Old Hand

    Points: 340

    Gud Quest !! 🙂 I have learnt about casting while trying to solve the error in the Quest... and by the way, can anyone give me the syntax for using 'DATEADD' function??

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    nithya.s (4/28/2010)


    Gud Quest !! 🙂 I have learnt about casting while trying to solve the error in the Quest... and by the way, can anyone give me the syntax for using 'DATEADD' function??

    Here you go:

    http://msdn.microsoft.com/en-us/library/ms186819.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Niths

    Old Hand

    Points: 340

    Cool !!!! Thanks a lot !! :-):-D

    ------------------------
    ~Niths~
    Hard Work never Fails 🙂

  • M@N@

    Valued Member

    Points: 52

    Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY

    DECLARE @Today DATE

    SELECT @Today = '01 DEC 2009'

    SELECT @Today+1

    SELECT @Today

    IF use @Today as DATE datatype we will get error like

    'Operand type clash: date is incompatible with int'

    so if change Datatype DATE to DATETIME, then it will work and we will get Result

    '2009-12-02 00:00:00.000'

    Best Regards

    M@N@

    [font="Verdana"]Thanks & Regards
    M@N@[/font]

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    M@N@ (4/28/2010)


    Yes, you are right,The 'DATE' datatype is exists on SQL Server 2005/2008 but i told that in below QUERY

    DECLARE @Today DATE

    SELECT @Today = '01 DEC 2009'

    SELECT @Today+1

    SELECT @Today

    IF use @Today as DATE datatype we will get error like

    'Operand type clash: date is incompatible with int'

    so if change Datatype DATE to DATETIME, then it will work and we will get Result

    '2009-12-02 00:00:00.000'

    Best Regards

    M@N@

    Well, thats kind the idea of the question, isn't it? You just explained the answer that was already there.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • kaspencer

    SSCarpal Tunnel

    Points: 4317

    That question revealed quite a significant difference between SQL Server 2005 and 2008.

    With SQL Server 2005, the datatype DATE is not valid, and so if the datatype of the variable is amended to DATETIME, the entire code set succeeds, and gives the answer 2nd December 2009, in whatever format. The datepart of the value to be added defaults to DAY.

    Yet, SQL 2008 is more rigorous in it's insistence on a correct datatype and datepart for the added day, when the datatype of the variable is DATE.

    Something to watch out for there, eh!

    Kenneth Spencer

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • OCTom

    SSChampion

    Points: 11755

    Good question. The difference between DATE and DATETIME in SQL Server 2008 is important. Thanks.

    The answer 40147 looked familiar to me. It is what I have known to be called a "hundred-year" format date. I have seen it used on the IBM i. 40147 is that format equivalent to 2009-12-01. It's not really important. I just found it interesting.

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

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