I'm having trouble wrapping my head around using a variable to store a function. I want to store the following function in a variable:
declare @agegroup int
set @agegroup = datediff(year, convert(date, p.date_of_birth), getdate())
Doing this produces an error:
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I believe that this is happening because SQL doesn't know what p.date_of_birth is yet. It's a column from a table in a query.
select p.date_of_birth from person p
I will need to reference the function in the variable numerous times in the query (once it's complete), so I want to neaten up my query by using a variable. It this possible?
Edit: I was asked to include some data for the field in my sample code. The DOB is stored as a string: yyyymmdd