SQL: Error converting varchar to int

Question

I have a following conversion error

Conversion failed when converting the varchar value '2016-October' to data type int.

in the following statement:

declare @month varchar(20) = 'October'
select HolidayName, Holiday
from tblHoliday 
where PortfolioID = 2 and DATEPART(Year, Holiday) = case when @month is null then 2016 else convert(varchar,2016) + '-' + @Month end

What am I doing wrong and what is the right way to do something like this?

Answer 1

I guess you want to archive:

Your scenario

1. if @month is null --> search all value in year 2016
2. if @month is not null --> search all value with month and year

Your issue:

1. when @month is not null, your query will be:

   ... and DATEPART(Year, Holiday) = convert(varchar,2016) + '-' @Month which is comparing between integer and varchar

2. There is no build in function in tsql that allow you to convert month name straight to integer, so a workaround is:

   SELECT DATEPART(MM, @month + '01 2000')

3. I don't think using case...when...then would work here since it compares differently in each of your scenario so i suggest using if...else

Code sample

declare @month varchar(20) = 'October'
if @month is null begin
    select HolidayName, Holiday
    from tblHoliday 
    where PortfolioID = 2
    and DATEPART(Year, Holiday) = 2016
end
else begin
    select HolidayName, Holiday
    from tblHoliday
    where PortfolioID = 2
    and DATEPART(Year, Holiday) = 2016
    and DATEPART(Month, Holiday) = DATEPART(MM, @month + '01 2000')
end