[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

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

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
robthomas09
Asked:
robthomas09
3 Solutions
 
knightEknightCommented:
-- 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
 
mlmccCommented:
Here is a formula in Crystal to calculate the age

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

mlmcc
0
 
Kurt ReinhardtCommented:
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
 
robthomas09Author Commented:
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now