SQL Server: passing string from variable into date - Conversion failed when converting date and/or t-CodePudding

I'm looking to take a variable (in this case, a 4-digit year value) and pass it into a query where we're searching for rows based on a date, yet SQL Server seems to be having trouble taking that 4-digit year and putting it into the date field.

Here's an example:

DECLARE @yearstart AS VARCHAR(10) = '2022'
DECLARE @yearend AS VARCHAR(10) = '2023'
....

WHERE date_of_service BETWEEN '@yearstart-10-01' AND '@yearend-09-30'

SQL Server throws an error:

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

Can anyone assist? Thank you in advance.

I was expecting 2022 to be passed in as plaintext to the query and just fill in the @yearstart and @yearend with 2022 and 2023 respectively.

CodePudding user response：

Use numeric parameters and DATEFROMPARTS instead :

DECLARE @yearstart int = 2022
DECLARE @yearend int= 2023

....

where date_of_service 
between DATEFROMPARTS(@yearstart,10,1) and DATEFROMPARTS(@yearend,9,30)

Parameters aren't format placeholders. Their values never become part of the query itself. The server compiles the SQL query into a reusable execution plan with parameters and only then executes it passing the parameter values to it. Next time the same query is encountered the server reuses the already compiled execution plan.