You know the story. A report is spitting out the wrong dates. Someone gets called in to investigate what’s wrong. After digging through piles of deeply nested SQL queries and views and trying to track down the problem, it turns out someone wrote their own date handling code which is wrong.

Darin P found the code this time.

SET @EDate = (SELECT CAST(CAST(CASE WHEN MONTH(GETDATE())-1 = 0 THEN 12 ELSE MONTH(GETDATE())-1 END AS NVARCHAR) 
	+ '/' +
	CASE WHEN MONTH(GETDATE())-1 = 2 THEN '28'
	WHEN MONTH(GETDATE())-1 = 4 THEN '30'
	WHEN MONTH(GETDATE())-1 = 6 THEN '30'
	WHEN MONTH(GETDATE())-1 = 9 THEN '30'
	WHEN MONTH(GETDATE())-1 = 11 THEN '30'
	ELSE '31'
	END
	+ '/' + CAST(CASE WHEN MONTH(GETDATE())-1 = 0 THEN YEAR(GETDATE())-1 ELSE YEAR(GETDATE()) END AS NVARCHAR(50)) AS DATETIME))

I don’t want to leap to any conclusions, but I think this code does a bad job of calculating how many days are in a given month. Hopefully they fix it before 2020.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!