I was running a query today and was not able to SUM by the current month. I checked the code and didn’t see any issues there, but when I looked at the variables it was pretty obvious.
DECLARE @targetdate VARCHAR(8)
DECLARE @previousdate VARCHAR(8)
DECLARE @currentmonth INT
DECLARE @currentday INTSET @targetdate = CONVERT(VARCHAR(8), GETDATE() – 1, 112)
SET @previousdate = CONVERT(VARCHAR(8), GETDATE() – 2, 112)
SET @currentmonth = ( 10 * CAST(DATEPART(Year, GETDATE() – 1) AS INT) ) + CAST(DATEPART(Month, GETDATE() – 1) AS INT)
SET @currentday = CAST(DATEPART(Day, GETDATE() – 1) AS INT)
Which results in:
20111018
20111017
20120
18
I was setting @currentmonth incorrectly. The correct value is:
SET @currentmonth = ( 100 * CAST(DATEPART(Year, GETDATE() – 1) AS INT) ) + CAST(DATEPART(Month, GETDATE() – 1) AS INT)
Which gives the correct date/month
20111018
20111017
201110
18
Big difference!