Age Calculation

Jeff Mason · November 21st, 2003, 08:13 PM

Quote:

quote:Originally posted by rgerald
DateDiff is one of those tricky functions that looks simple but isn't.

SELECT DateDiff(dd, '12/31/2003', '01/01/2004') returns 1 (day)
SELECT DateDiff(mm, '12/31/2003', '01/01/2004') also returns 1 (month)
SELECT DateDiff(yyyy, '12/31/2003', '01/01/2004') also returns 1 (year)

Yes. Just remember that DATEDIFF counts boundary crossings - it doesn't calculate the true difference, whatever you think that might be. :)

Quote:

quote:
Another handy one would be a DateFrac(datetime) function which would return a datetime datatype with the time set to 00:00:00.

This you can do by CONVERTing the datetime to a character string and then CASTing the result back to datetime. By default, if you do not supply a time to a datetime, the time portion of the datetime value is set to midnight (00:00:00). Similarly, if you do not supply a date, it is set by default to Jan 1, 1900.

The expression would be along the lines of:

Code:

CAST(CONVERT(char(8),thedate,112) as datetime)

This first CONVERTs the datetime to yyyymmdd format, then CASTs it back to a datetime, effectively discarding the time portion (or more correctly, setting it to midnight)

Quote:

quote:
And the converse TimeFrac(datetime) function would return a datetime datatype with the date set to 01/01/1900 (day zero).

It is left as an exercise to the reader to look up the style parameter to the CONVERT function which only returns a time, and use the same expression form to discard the date (set it to 1/1/1900). :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Gert · November 25th, 2003, 12:18 PM

Some time ago I wrote the code below to solve this kind of problem. It might not be very elegant, but maybe you could use it somehow. I think it should work on all dates (where fromdate<todate), but it is a while since I have tested it...

Here goes:

Code:

--dateformat= yyyymmdd
declare @from datetime set @from = '20031112'
declare @to datetime set @to = '20031211'

declare @year int set @year=0
declare @month int set @month=0
declare @day int set @day=0
declare @rndate datetime     
set @rndate=@from --rndate = runningdate

--calculate the @year 
if(datepart(year,@to)>datepart(year,@from))
begin
    set @year = datediff(year,@from,@to)
    set @rndate = dateadd(year,@year,@from)    --update runningdate
    if(@rndate>@to)
    begin
        set @year = datediff(year,@from,dateadd(year,-1,@to)) --calculate years from @from to @to - 1 year
        set @rndate = dateadd(year,@year,@from)    --update runningdate
    end

end

--add 1 month as long as running date is smaller than or equal to @to
while @rndate<=@to
begin
    set @rndate = dateadd(month,1,@rndate)
    if (@rndate<=@to)
    begin 
        set @month=@month+1
    end
end
--set @rndate back 1 month
set @rndate=dateadd(month,-1,@rndate)

--start to count days
while @rndate<@to
begin
    set @rndate=dateadd(day,1,@rndate)
    set @day=@day+1
end

select @year as Years,@month as Months,@day as Days

Gert

Geoff Scott · December 3rd, 2003, 04:50 PM

Example: You have a table called "tabAge" with two fields, one called "Age" the other called "DOB".
You have two records in your table.
The first record has nothing in the Age field, but in the DOB field you enter a date that is ten years ago, minus 1 day. (in other words, ten years ago, tomorrow) example: say today is the 3rd Dec then the date you enter is 4th Dec 1993.
The second record has nothing in the Age field, but in the DOB field you enter a date that is ten years ago exactly. as example above your date would be 3rd Dec 1993.
You will now be able to check that your code is working correctly, TO THE DAY.
You now create an Update query, with the two fields from your table. Then using the SQL statement as the Update To instruction and enter (copy and paste) the following instruction into the SQL window:

UPDATE tabAge SET tabAge.Age = (DatePart("yyyy",Date())-DatePart("yyyy",[DOB]))+(Date()<(DateSerial(DatePart("yyyy",Date()),Dat ePart("m",[DOB]),DatePart("d",[DOB]))));

The display above has put this instruction onto two lines, but if you copy and paste the two lines at one go, it will return to a one line instruction.
When the query is Run, the exact correct age will be inserted into the Age fields of your table.

You can find an explanation of how this code works at my website.

rgerald · December 3rd, 2003, 05:14 PM

Another problem with smalldatetime is that it cannot handle dates prior to 01-Jan-1900. Our database has a few super-centenarians who were born before that date. Excel also has that limitation. When we put data into a spreadsheet, the DoB for those people is lost!

Rand

6stringchris · July 19th, 2007, 04:46 PM

Quote:

quote:Originally posted by rgerald
DateDiff is one of those tricky functions that looks simple but isn't.

Ha... no kidding!

Im new to vb.net so please forgive any retarded-ness on my part, but what about figuring age in days, then divide by 365? This would avoid the "Dec 31 to Jan 1" problem?
Thanks for any input. Seems simpler from logic, but I havent got it to work in VB yet.

Chris

happygv · July 20th, 2007, 01:38 AM

Chris, Then you should still be using the same Datediff with first parameter as "day" in place of "year". Something like this.

Code:

dateadd(day, <fromdate>, <today>)

_________________________
- Vijay G
Strive for Perfection

rgerald · July 20th, 2007, 08:49 AM

Here is what we use to calculate age:

Code:

CREATE FUNCTION [dbo].[f_AgeCalc] (
@DateOfBirth DateTime = NULL,
@RefDate DateTime = NULL)
RETURNS int AS  
BEGIN
RETURN (SELECT CASE
WHEN ((@DateOfBirth IS NULL) OR (@RefDate IS NULL))
THEN NULL
WHEN (@DateOfBirth > @RefDate) THEN NULL
WHEN DateAdd(yyyy, DateDiff(yyyy, @DateOfBirth, @RefDate), @DateOfBirth) > @RefDate
THEN DateDiff(yyyy, @DateOfBirth, @RefDate) - 1
ELSE DateDiff(yyyy, @DateOfBirth, @RefDate) 
END)
END

It gives the age of a person (in whole years) as of a reference date - usually GetDate().

For example:

Code:

SELECT dbo.f_AgeCalc('1809.02.12', GetDate())

SELECT dbo.f_AgeCalc('1809.02.12', '1865.04.15')

Abraham Lincoln would be 198 years old today and he ws 59 when he died. Note that SQL Server works fine with dates prior to the twentieth century - since 1753.

Rand

Peso · July 21st, 2007, 04:47 PM

See this
http://www.sqlteam.com/article/dated...on-demystified

rgerald · July 23rd, 2007, 08:52 AM

The problem with calculating the date difference in days and then dividing by 365 is that it doesn't correct for leap years and yields a value that is 0.04% too high. Furthermore, the correction for leap years can be very complicated if done that way.

Rand

happygv · July 23rd, 2007, 09:21 AM

Does that DateDiff function with YEAR parameter not handle the leap year part of it? I dont understand why would one need to calculate it in days and then divide by 365? Am I lost somewhere in the middle? Sorry if so...

Cheers.

_________________________
- Vijay G
Strive for Perfection