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

Question

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.

where date_of_service between cast(@yearstart + '-10-01' as date) and cast(@yearend+'-09-30' as date) You need to use CAST() — Anel Hodžić
– Anel Hodžić, Commented Jan 31, 2023 at 14:58
T-SQL is a compiled language and doesn't support variable replacement like a scripting language. '@yearstart-10-01' isn't read as a literal string consisting of the value of the variable @yearstart and the literal value '-10-01', it's just interpreted as the literal value '@yearstart-10-01'. That literal, unsurprisingly, isn't a valid date and time value. — Thom A
– Thom A ♦, Commented Jan 31, 2023 at 15:12

Panagiotis Kanavos · Accepted Answer · 2023-01-31 15:04:08Z

4

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.

answered Jan 31, 2023 at 15:04

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related