DATEADD doesn't throw exception on invalid datepart - by richbrownesq

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 596764 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 9/10/2010 3:24:03 AM
Access Restriction Public


SELECT DATEADD(Y,-1,DateColumn) doesn't return the correct data. It turns out that using the datapart Y in this context actually runs as if it were D.

I checked the documentation in BOL which states that YY or YYYY are valid dateparts for year which i accept. But it surprises me that Y doesn't work and worse than that, doesn't throw an error. If i use the datapart X i get an error "X' is not a recognized dateadd option".
Sign in to post a comment.
Posted by Microsoft on 9/13/2010 at 1:16 PM
We have resolved the issue as "by design" and closed it. As Erland mentioned earlier, "y" is short form for dayofyear. So it is valid argument. Our recommendation is to use the fullname instead of abbreviations to avoid confusion and improve readability.

Umachandar, SQL Programmability Team
Posted by Erland Sommarskog on 9/12/2010 at 5:46 AM
Books Online includs this line:

    dayofyear     dy, y

That is, this is not a bug, but documented behaviour. Whether it is good can be disputed, but best practice is certainly to spell out the part in full. YEAR is not that difficult to type.
Posted by Jamie Thomson on 9/10/2010 at 4:45 AM
Bad error this. I wonder though if they'll actually fix it on the grounds that (however small the chance) it could break existing code.

Yet another reason why we need a brand new language to replace T-SQL.