Home Adding a function to a variable in SQL Server 2008
Reply: 1

Adding a function to a variable in SQL Server 2008

theeviininja Published in 2018-02-13 16:01:55Z

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

Gordon Linoff
Gordon Linoff Reply to 2018-02-14 02:41:25Z

First, I assume your code is taken out of context, because p is not defined. That's okay.

Your problem is that date_of_birth is stored as a string. Fix that problem, by storing it as a date. SQL databases have date/time types for a reason. Use them.

You can eliminate the error in your code by using try_convert():

set @agegroup = datediff(year, try_convert(date, p.date_of_birth), getdate())

In SQL Server 2008, a reasonable approximation is:

set @agegroup = datediff(year, (case when isdate(p.date_of_birth) = 1 then p.date_of_birth end), getdate())

You can identify the offending values by doing:

select date_of_birth
from p
where try_convert(date, date_of_birth) is null and date_of_birth is not null;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.334397 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO