Solved

Crystal Reports 11 - Find person's age from date of birth - SQL Server 2005

Posted on 2011-03-10
4
1,086 Views
Last Modified: 2012-05-11
Hello experts,

I have a need to display the customer's age in years.  The field in the database is person.date_of_birth, and is an 8 character varchar that stores Date of Birth as yyyymmdd.

What is the best way to display the current age?

Something like:

DateDiff("yyyymmdd", CDate({person.date_of_birth}), CurrentDate ), 0) + " yrs"

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
4 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 167 total points
ID: 35100688
-- Here is a straight SQL approach - you would substitute the DOB column name for @bd in the query:

declare @bd varchar(10)
select  @bd = '2008-02-21'

select datediff(yy,@bd,getdate())
  - case when month(@bd)>month(getdate()) then 1 else 0 end
  - case when month(@bd)=month(getdate()) and day(@bd)>day(getdate()) then 1 else 0 end
as Age
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 35101334
Here is a formula in Crystal to calculate the age

http://www.kenhamady.com/form06.shtml

mlmcc
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 166 total points
ID: 35101932
I have a presentation that that discusses not only the most commonly used methods for calculating age accurate, but also demonstrating how they're wrong most of the time. The presentation also demonstrates how to accurately calculate age with both a formula and a SQL Expression.  The problem with using a formula is that, while accurate, it can't be used efficiently within the record selection criteria - if you need to filter a report against people aged 5 - 10, for example, the formula won't be processed on the database.  That's where the SQL Expression comes in.

Check out the presentation: https://www.box.net/shared/ugmisbn6tz

~Kurt
0
 

Author Closing Comment

by:robthomas09
ID: 35110037
Thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now