SQLTeam.com | Weblogs | Forums

Date diff


if my as of date is 20151001

should this part of the query hit

TotalReturn9Months =
               WHEN As_of_Date > DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000701'')
AND As_of_Date <= DATEADD(yy,DATEDIFF(yy,0,[Date_To]),''19000930'')


date_to is 20151230


Pretty sure you shouldn't be doing that. Testing with datediff of X years if not very reliable, and your conditions are pretty rough to understand which is going to cause you trouble when debugging it.

Tell us what you want to do instead!


i didnt write the code. i was just asked to make sure that if with those dates for as of date and to date that it shouldn't match up to total 9 months. i dont think it does but just wanted to be sure


That's actually a fairly common technique for adjusting dates in SQL Server.

But that particular code won't give you a nine month spread in dates, as you can verify by SELECTing the results of the date calcs. It appears this code was originally intended to produce quarterly totals?!

select DATEADD(yy,DATEDIFF(yy,0,[Date_To]),'19000701'),
from (select cast('20151001' as date) as date_to) x



thanks for reply. yes this was intended and still is intended to produce quarterly totals.

what would be the best way to explain that to someone who didnt know much about coding and sql


is it that it requires the date from the datediff of date_To to be between july & sept