?
Solved

Calculate Age Groups

Posted on 2012-09-03
16
Medium Priority
?
558 Views
Last Modified: 2012-09-06
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

0
Comment
Question by:pposton
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38362103
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
 
LVL 18

Accepted Solution

by:
Matthew Kelly earned 2000 total points
ID: 38362160
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
 

Author Closing Comment

by:pposton
ID: 38362183
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38362191
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38362195
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
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 38370531
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
 

Author Comment

by:pposton
ID: 38370581
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
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 38370647
What is the error you receive?
0
 

Author Comment

by:pposton
ID: 38370656
"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
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 38370689
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
 

Author Comment

by:pposton
ID: 38370718
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38372149
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
 

Author Comment

by:pposton
ID: 38372383
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38373542
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
 

Author Comment

by:pposton
ID: 38373712
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38374614
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question