Adding a function to a variable in SQL Server 2008

Question

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

No, this is happening because you are storing a date as a string -- a no-no -- and then trying to convert it to a date. The format is invalid. — Gordon Linoff
– Gordon Linoff, Commented Feb 13, 2018 at 16:02
set @agegroup = datediff(year, convert(date, p.date_of_birth), getdate()) doing this will NEVER produce this error Conversion failed when converting date and/or time from character string. — Pரதீப்
– Pரதீப், Commented Feb 13, 2018 at 16:03
@Pரதீப் Really? What if date_of_birth is '20180231'? — Aaron Bertrand
– Aaron Bertrand, Commented Feb 13, 2018 at 16:04
There's nothing wrong with SELECT CONVERT(date,'18840218');. I'm still very much of mind that you have a fictitious date in your data. — Thom A
– Thom A ♦, Commented Feb 13, 2018 at 16:55

Gordon Linoff · Accepted Answer · 2018-02-14 02:41:25Z

3

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;

edited Feb 14, 2018 at 2:41

answered Feb 13, 2018 at 16:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

theeviininja Over a year ago

Hi Gordon, Is there any way to get around this problem? I can't edit the structure of this table (it's proprietary). Can't I try converting the column to date in a var first?

Aaron Bertrand Over a year ago

@theeviininja He already showed you a way to get around the problem - add try_convert() to your query. Of course you could fix the data in the table, even if you can't fix the data types, but you'll have to do this over and over again unless you also put something in place to prevent more bad data from getting in there in the first place.

theeviininja Over a year ago

@GordonLinoff I forgot to mention that this is SQL Server 2008, not 2012, so Try_Convert isn't an option, unfortunately. I updated the title of my post.

Gordon Linoff Over a year ago

@theeviininja . . . You should include sample data in the question so we know what the dates are supposed to look like.

Aaron Bertrand Over a year ago

@theeviininja On ancient versions like that you can use CONVERT(date, CASE WHEN ISDATE(p.date_of_birth) = 1 THEN p.date_of_birth END) which is essentially what TRY_CONVERT() is doing. But you need to (a) clean up the data (identify it with WHERE ISDATE(date_of_birth) = 0) and (b) go back to the vendor and tell them their design is moronic.

Collectives™ on Stack Overflow

Adding a function to a variable in SQL Server 2008

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related