Use a variable inside a conversion function-CodePudding

I have a query that is getting a list of dates. I need each value to be within a particular month. My Completed field is a DATETIME data type, so I am cutting the time portion off. My results image shows the output from the my query.

Am I able to replace the -1 in my StartOfMonth & EndOfMonth variables with my MonthsToGoBack variable so that I only have to change its value if I want a different month?

 = CONVERT(CHAR(10), DATEADD(m, @MonthsToGoBack, DATEADD(

results

DECLARE 
    @MonthsToGoBack INT = -1,
    @StartOfMonth DATE = CONVERT(CHAR(10), DATEADD(m, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)), 101),   -- first day of the month
    @EndOfMonth DATE = CONVERT(CHAR(10), DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()),0)), 101)       -- last day of the month
;
 
SELECT DISTINCT CONVERT(VARCHAR, CompletedDate, 101) AS CompletedDate 
FROM MyTable
WHERE CONVERT(DATE, CompletedDate) BETWEEN @StartOfMonth AND @EndOfMonth

I am currently seeing this error when I try it.

Msg 137, Level 15, State 2, Line 5
Must declare the scalar variable "@MonthsToGoBack".
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@StartOfMonth".

CodePudding user response：

Don't try and do all of the logic to set the variable's value in the declaration - you can DECLARE a variable first, and then SET its value in a subsequent statement.

DECLARE 
    @MonthsToGoBack INT = -1,
    @StartOfMonth DATE,
    @EndOfMonth DATE;

    SET @StartOfMonth = CONVERT(CHAR(10), DATEADD(m, @MonthsToGoBack, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)), 101);

Now the value of @StartOfMonth is set as you expect. You can then use the same SET command to set the appropriate value of other variables, and then use them in later statements.