Tuning sql by year

Question

I have sql query like this where the input is @year = 2017 while the column format is '2017-01-01 05:02:45.000'. And I would like to tune this query because It has very long execution time.

DECLARE @Device_List VARCHAR(500) = 'MKV005, MKV007, NWTN01, NWTN03, QUEEN02, MKV009';

DECLARE @YEAR VARCHAR(20) = '2017'

SELECT MONTH(deduction_timestamp) as [Month],
      ISNULL(sum(fare_deduction), 0) AS total_fare_deduction
FROM [dbfastsprocess].[dbo].[vClosingTransitLog]
WHERE bus_id in (select * from fnSplit(@Device_List, ','))
    and YEAR(deduction_timestamp) = ISNULL(@Year, YEAR(deduction_timestamp))
GROUP BY MONTH(deduction_timestamp)
ORDER BY [Month]

and would like to do like this

SELECT MONTH(deduction_timestamp) as [Month],
      ISNULL(sum(fare_deduction), 0) AS total_fare_deduction
FROM [dbfastsprocess].[dbo].[vClosingTransitLog]
WHERE bus_id in (select * from fnSplit(@Device_List, ','))
    and (deduction_timestamp) >= '@year-01-01 00:00:00' and
 (deduction_timestamp) < '@year(plus one year)-01-01 00:00:00'
GROUP BY MONTH(deduction_timestamp)
ORDER BY [Month]

But currently It doesn't work because of error

Conversion failed when converting date and/or time from character string.

Can you guys help me? Really appreciate it. Thanks

Answer 1

You need to concatenate the year variable with the rest of your string, you can't embed it into the string.

deduction_timestamp < CONVERT(DATETIME, @year + '-01-01 00:00:00')