[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-03-10
4
Medium Priority
?
1,117 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 668 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 101

Assisted Solution

by:mlmcc
mlmcc earned 668 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 664 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

612 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