Solved

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

Posted on 2011-03-10
4
1,094 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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