Tuesday, 14 September 2010


Just trying to troubleshoot an issue with one of my developers and came across this "feature" with DATEADD.

He had something akin to, SELECT DATEADD(Y,-1,DateColumn) and couldn't understand why it wasn't returning 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 DY or Y is a valid datepart which i don't really understand. DY means day of year which isn't really appropriate for a DATEADD statement. If it behaves like DAY then i'd expect that to be in the documentation.

Looking around connect, it appears that this is a problem with the vbscript implementation of dateadd too so my guess is that this error has been "ported".

I raised a connect case #596764 but it was closed as "by design". I guess its low priority and strictly speaking it shouldn't throw an error as Y is a valid datepart. Still, i don't believe the documentation is clear and i also don't believe that it should behave like this.

No comments:

Post a comment

/* add this crazy stuff in so i can use syntax highlighter