DATEADD syntax for SQL Server 2008


  • The MS SQL 2008 Books Online article for DATEADD (found at gives this as an example of valid syntax:

    SELECT DATEADD(minute, 1, ' 2007-05-07 09:53:01.0376635');

    However, running the command as a query produces this error: Conversion failed when converting date and/or time from character string

    The article mentions that if the expression being used is a string literal, it will resolve to datetime.  Datetime does not support the precision which is given in the example above, so there appears to be a contradiction here.

    Can you please clarify?  Am I misunderstanding the given example?  Also, is it at all possible to use syntax like the one in the SELECT statement above against a datetime2 value without declaring it as a variable first?


    Friday, March 20, 2009 10:27 PM

All replies

  • I think that kind of precision is only reserved for the new DATETIME2 datatype.

    In your statement above, it assumes you want a DATETIME.

    If you instead do   SELECT DATEADD(minute, 1, CAST('2007-05-07 09:53:01.0376635' AS datetime2))    then it'll probably work.

    Friday, March 20, 2009 11:08 PM
  • Alex P.,

    That example is not correct.

    DATEADD (Transact-SQL)

    The explanation about the date parameter states that "If the expression is a string literal, it must resolve to a datetime.".

    You can use "send feedback" button, at the end of the page, to report this to the documentation team.

    Saturday, March 21, 2009 12:16 AM
  • Try this,

    Syntax for DATEADD
    DATEADD (datepart , number, date )

    SELECT DATEADD(minute, 1, '2007-05-07 09:53:01.037')

    for more info check

    Rajesh Jonnalagadda
    Saturday, March 21, 2009 4:56 AM
  • Thanks for the suggestions, the syntax using CAST was very helpful.

    A follow up question... this 'datetime' restriction does not exist for DATEDIFF or DATEPART functions.  Why the discrepancy, it seems like these functions should have similar limitations?
    Tuesday, March 31, 2009 8:41 PM
  • I would say it's for backward compatibility, I suppose, that DATEADD requires a string literal to resolve to datetime as opposed to datetime2.

    Also, think about what the functions are doing...

    DATEADD takes a date, adds stuff to it, and RETURNS A DATE that is (important) in the same format as the date that was passed to the function.

    DATEDIFF takes two dates (doesn't matter what type they are... smalldatetime, datetime, datetime2) and subtracts the element you desire (day, minute, hour, etc) to return an INTEGER.  When it does the subtraction, (quoted from books online):  If startdate and enddate are of different date data types and one has more time parts or fractional seconds precision than the other, the missing parts of the other are set to 0.

    DATEPART just takes a date (in whatever format) and returns the element (day, minute, hour) you desire as an INTEGER.  The datatype of the date doesn't matter, because they all have consistent "parts".  Again, same rule as DATEDIFF:  if you try to return nanoseconds from a smalldatetime or datetime, it will just return 0.

    Tuesday, March 31, 2009 8:53 PM