The MS SQL 2008 Books Online article for DATEADD (found at http://msdn.microsoft.com/en-us/library/ms186819.aspx) gives this as an example of valid syntax:
SELECT DATEADD(minute, 1, '
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?
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.
That example is not correct.
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.
Syntax for DATEADD
DATEADD (datepart , number, date )
SELECT DATEADD(minute, 1, '2007-05-07 09:53:01.037')
for more info check
Rajesh Jonnalagadda http://www.ggktech.com
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?
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.