Calculate Age Groups

I am trying to calculate  the age of a client from a varchar column that contains the date of birth.  Many of the rows are NULL.  

Once I calculate the age I then need to place them into age groups and count the results.  ie. 18-21 = 3, 25-30=10, etc.  

Below is the code I am currently trying, however I get the following error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Any help on fixing this code (or something all together different is fine also) will be appreciated.  

Thanks!

select age_group
, count(*) as rows
  from (
       select case when (convert(varchar (10),(datediff(year,DOB,getdate()))))
                          between 18 and 21 then '18-21'
                   when (convert(varchar (10),(datediff(year,DOB,getdate()))))
                          between 25 and 30
                   then '25-30'
                   when (convert(varchar (10),(datediff(year,DOB,getdate()))) )
                          between 35 and 40
                   then '35-40'     
                   else null end   as age_group                   
                   
                   
         from client
       ) as T
 where age_group is not null
group
    by age_group

Open in new window

ppostonPresident/OwnerAsked:
Who is Participating?
 
Matthew KellyConnect With a Mentor Commented:
You can check if it is a date first... something like below. However, you will really want to go through the data and see what non-date values are there, why they are there, and make sure there won't be more added in the future (ie perhaps by adding input validation).

http://msdn.microsoft.com/en-us/library/ms187347.aspx

select age_group
, count(*) as rows
  from (
       select 
case when ISDATE(DOB) = 1 then
case when (convert(varchar (10),(datediff(year,DOB,getdate()))))
                          between 18 and 21 then '18-21'
                   when (convert(varchar (10),(datediff(year,DOB,getdate()))))
                          between 25 and 30
                   then '25-30'
                   when (convert(varchar (10),(datediff(year,DOB,getdate()))) )
                          between 35 and 40
                   then '35-40'     
                   else null end   
else null end
as age_group                   
                   
                   
         from client
       ) as T
 where age_group is not null
group
    by age_group

Open in new window

0
 
lwadwellCommented:
What format are the DOB values?  Are the dd-mm-yyyy, mm-dd-yyyy, yyyy-mm-dd, one of those but with '/' instead or something different?  Are they consistent in their format?
0
 
ppostonPresident/OwnerAuthor Commented:
Thanks!  That works like a charm.  I have built validation into the updated program.  I just can't delete exisiting data or update null values due to the way the site works.  This will get what I need for a report, however.

Thanks for the quick answer!!
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
Just a word of caution, DATEDIFF() with a year parameter returns the number of years between two dates, so the following returns 1 (2012-2011 = 1) even though they are only one day apart:
SELECT DATEDIFF(Year, '20111231',  '20120101')

This is clearly not what you want when trying to calculate age.
0
 
Anthony PerkinsCommented:
Not to rain on your parade, but here is the exact definition for the DATEDIFF() function:
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
It is not the number of years between x and y.
0
 
Matthew KellyCommented:
http://msdn.microsoft.com/en-us/library/aa258269(v=sql.80).aspx

select age_group
, count(*) as rows
  from (
       select 
case when ISDATE(DOB) = 1 then
case when (convert(varchar (10),(datediff(day,DOB,getdate()))))
                          between 6570 and 7665 then '18-21'
                   when (convert(varchar (10),(datediff(day,DOB,getdate()))))
                          between 9125 and 10950
                   then '25-30'
                   when (convert(varchar (10),(datediff(day,DOB,getdate()))) )
                          between 12775 and 14600
                   then '35-40'     
                   else null end   
else null end
as age_group                   
                   
                   
         from client
       ) as T
 where age_group is not null
group
    by age_group

Open in new window

0
 
ppostonPresident/OwnerAuthor Commented:
Thanks for the update.  Unfortunately that works on my local server but not my hosted server.  When I origionally  closed this out I had just checked it out on my local machine.  Now I'm trying to figure out why it's not working after I upload it.

Any thoughts or help would be appreciated.
0
 
Matthew KellyCommented:
What is the error you receive?
0
 
ppostonPresident/OwnerAuthor Commented:
"Conversion failed when converting date and/or time from character string"

It's so odd because its just on the live server. By trial and error it's in this section:

),(datediff(day,DOB,getdate()))))
                          between 6570 and 7665 then '18-21'

I've checked with the hosting company and it doesn't seem to be an issue there since they are running SQL 2008r2 like I am on my machine.
0
 
Matthew KellyCommented:
I am assuming the data is different between the servers? It must be something with the data on the live server. Did it work with the year but not the day?

Here is updated that should calculate the year correctly. Don't have a server to try it on at the moment, so hopefully the parenthesis are correct.

select age_group
, count(*) as rows
  from (
       select 
case when ISDATE(DOB) = 1 then
case when (convert(varchar (10),(DateDIFF(yy,DOB,getdate())-CASE WHEN getdate()>=DateAdd(yy,DateDIFF(yy,DOB,getdate()), DOB) THEN 0 ELSE 1 END)))
                          between 18 and 21 then '18-21'
                   when (convert(varchar (10),(DateDIFF(yy,DOB,getdate())-CASE WHEN getdate()>=DateAdd(yy,DateDIFF(yy,DOB,getdate()), DOB) THEN 0 ELSE 1 END)))
                          between 25 and 30
                   then '25-30'
                   when (convert(varchar (10),(DateDIFF(yy,DOB,getdate())-CASE WHEN getdate()>=DateAdd(yy,DateDIFF(yy,DOB,getdate()), DOB) THEN 0 ELSE 1 END)) )
                          between 35 and 40
                   then '35-40'     
                   else null end   
else null end
as age_group                   
                   
                   
         from client
       ) as T
 where age_group is not null
group
    by age_group

Open in new window

0
 
ppostonPresident/OwnerAuthor Commented:
No...the data is the same.  It's just a copy of what I have on my machine.  That's what's so odd.  On a procedure I did about a week ago I had a line in my code that had this: "activity..gift" .  It would work on my local machine but not on the live server.  I removed the extra "." and it began  working correctly.  I'm just not sure why it wouldn't work once uploaded if it works on my machine.

I just tried your updated code and the same thing happens.  Works locally but not on the server.  Odd.

Is there a way to check for "bad" date data through a query that I could see if there is an offending culprit?
0
 
Anthony PerkinsCommented:
On a procedure I did about a week ago I had a line in my code that had this: "activity..gift" .  It would work on my local machine but not on the live server.  I removed the extra "." and it began  working correctly.  I'm just not sure why it wouldn't work once uploaded if it works on my machine.
Check the value of SET QUOTED_IDENTIFIER, I suspect they are different.
0
 
ppostonPresident/OwnerAuthor Commented:
No they're both the same...set to "ON".  The procedure is the exact same except for the name of the except for the name of the database.

It looks like when it hits the datediff section that it throws the error.  Odd since it is checking for isdate.  I've even added "LEN(DOB)=10 OR LEN(DOB)=8" to the isdate to filter it even more.  When I run just the isdate with the LEN to check data, it runs just fine and the data looks ok.  I tried running it with just the LEN(D)B)=10, that didn't work.  So for some reason it just doesn't want to either convert varchar or it doesn't want to calculate the datediff.
0
 
Anthony PerkinsCommented:
I guess I am being dense here.  Are you saying that DOB is not a datetime data type?  If it is why would you do LEN(DOB), if it is not than you should not be using DATEDIFF() without first validating it.
0
 
ppostonPresident/OwnerAuthor Commented:
Yes the data is stored as varchar and it does contain data that may be erroneous.  The stored procedure is one that I recieved help on and origionally I thought it was working.  Well it was, but only on my local machine not from the server through my website.

I thought the isdate statement would filter out all non date data before running it through the datediff?
0
 
Anthony PerkinsCommented:
You need use ISDATE() as well as do an explicit CONVERT() from your specific format.  Don't rely on implicit conversions or you will run into trouble.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.