Last date of the previous month in a variable

21

If you use CONVERT to convert the date to a char/varchar you don't have to worry about inserting a '0' into the string, which simplifies the process.

Instead of defining a variable to hold that value in your query - you can just as easily use the CONVERT statement directly.

WHERE someDateColumn = convert(char(7), @date, 111)
WHERE someDateColumn = replace(convert(char(7), @date, 121), '-', '/')

However - if someDateColumn is defined as varchar(10) then the above would be implicitly converted to the appropriate data type, so an additional cast/convert would be needed. In that case - a variable is a better option:

DECLARE @lastMonthString varchar(10) = convert(char(7), @date, 111);

Now - when you use that variable you will be comparing a varchar(10) variable with a varchar(10) column which makes sure you can use indexes on that column if they exist.

22

Thanks very helpful.

Something else please, does YEAR(@Date) and MONTH(@Date) return int or date data_types?

23
1 Like