Solved

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

Posted on 2011-03-10
4
1,085 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
Comment Utility
-- 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

7 Experts available now in Live!

Get 1:1 Help Now