When would I use this?
Say you have an SSRS report which provides users with the parameter options month and year and the report returns data for that month and year. You will likely need that month name (varchar) and year (int) to be converted into a date fields representing the first and last day of that month/year. Once you have those two dates they can then be used in the WHERE clause of your SQL query to return data within that range. The logic below will allow the conversion of month name and year into start and end dates described above. You can now take this logic and input it into a stored procedure or user defined function.
There will only ever be 12 months going forward but to create an ever updating parameter option for years please see this tutorial.
DECLARE @year AS INT DECLARE @month AS VARCHAR(9) DECLARE @monthNumber AS CHAR(2) DECLARE @startDate AS DATE DECLARE @endDate AS DATE; SET @year = 2016 SET @month = 'February' IF @year IS NOT NULL BEGIN WITH monthPicker AS ( SELECT CASE WHEN @month = 'January' THEN '01' WHEN @month = 'February' THEN '02' WHEN @month = 'March' THEN '03' WHEN @month = 'April' THEN '04' WHEN @month = 'May' THEN '05' WHEN @month = 'June' THEN '06' WHEN @month = 'July' THEN '07' WHEN @month = 'August' THEN '08' WHEN @month = 'September' THEN '09' WHEN @month = 'October' THEN '10' WHEN @month = 'November' THEN '11' WHEN @month = 'December' THEN '12' ELSE NULL END AS monthPicked ) SELECT @monthNumber = ( SELECT monthPicked FROM monthPicker ) SET @startDate = ( SELECT CAST(CAST(@year AS VARCHAR(4)) + @monthNumber + '01' AS DATETIME) ) SET @endDate = ( SELECT DATEADD(s, - 1, DATEADD(MM, DATEDIFF(M, 0, @startDate) + 1, 0)) ) END ELSE BEGIN SET @startDate = ( SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) ) SET @endDate = (DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, GETDATE()), - 1)) END PRINT @startDate PRINT @endDate