Calculating age from birthdate with a formula

[JD5241]

XI - SQL

I would like to be able to determine people's ages from their birthdates in a Crystal formula.
I have a rather long SQL statement that will do it outside of Crystal, but I'm not sure how to alter it to work within Crystal, if that's even the direction I should be going in.
The birthdate in the field (table.DOB) I'm working with is in 'YYYYMMDD' format.

Here is the SQL code I have:

DECLARE @dob_input       CHAR(8)
       ,@dob_datetime    DATETIME
       ,@dob_day_of_year INTEGER
       ,@now_day_of_year INTEGER
       ,@age_in_years    INTEGER

-- input format yyyymmdd
SET @dob_input = '19710916'

-- convert input date format to SQL DATETIME format
SET @dob_datetime = CAST( SUBSTRING( @dob_input, 1, 4 ) + '-' + SUBSTRING( @dob_input, 5, 2 ) + '-' + SUBSTRING( @dob_input, 7, 2 ) AS DATETIME )

-- determine the birthdate 'day of year'
SET @dob_day_of_year = DATEPART( dy, @dob_datetime )

-- determine the current 'day of year'
SET @now_day_of_year = DATEPART( dy, CURRENT_TIMESTAMP )

-- Remove comment line below if you want to see the intermediate values.
--SELECT @dob_datetime, @dob_day_of_year, @now_day_of_year

-- calculate the number of years between date of birth and now (the person's age)
SET @age_in_years = CASE WHEN @dob_day_of_year > @now_day_of_year
                         THEN DATEDIFF( yy, @dob_datetime, CURRENT_TIMESTAMP ) - 1
                         ELSE DATEDIFF( yy, @dob_datetime, CURRENT_TIMESTAMP )
                    END -- CASE

SELECT @age_in_years

My end goal is to determine the age, then group the report by age groups. I've got the age group formula where I think it will work, it's just determining the age itself is where my problem lies. Any help is appreciated.

"I have no idea what's going on." -Towelie

 

[IdoMillet]

Use the DateDiff() function.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[JD5241]

The table.DOB field is pulled in as a string, not a Date or DateTime field (I should have specified that in the first place), so unless I convert it somehow, DateDiff() isn't recognizing it...

"I have no idea what's going on." -Towelie

 

[IdoMillet]

Use CDate() to convert to date.
Use CurrentDate to retutn today's date.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

After converting the stringdate to a date, you could use Ken Hamady's age formula:

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

-LB