Age Calculations

Correct Date Format   -  YYYY-MM-DD


To work with date fields in SQL, we'll use the Date and Time functions that Access supplies. Note that those functions are available in just about every environment that supports SQL.

The main functions: NOW( ) and DATE( ) return the current date. The difference between the two is that NOW( ) returns date and time, at this moment, and DATE( ) returns only the current date.

In Access, a date or time constant must be identified with # ... #, as in:
... WHERE p_startdate = #2001-01-01#;

In Access and SQL, one of the most useful functions is called: DateDiff( )

DateDiff('interval', #date1#, #date2#) returns the time difference between date1 and date2, expressed in interval units which could be: days, months, years, weeks or hours.

The interval is specified as: 'd' for days, 'w' for weeks, 'm' for months and 'yyyy' for years.

For example:

In theory, Datediff('yyyy', e_BirthDate, now()) returns the employee's age, expressed in years. In practice however, you will find that it works or doesn't work depending on whether the employee has had his birthday yet this year or not.

To calculate the exact age, use the following formula:

INT(Datediff('d', e_BirthDate, now())/365.25)

Calculate the number of days and divide by the exact number of days in a year, which, as you know, is 365.25 and not 365. That takes leap years into account.
The INT( ) function truncates the result so that 25.9 becomes 25, for example; the employee is 25 years old until the day she turns 26; after the age of 5, you rarely hear people say that they are 25 and a half years old.

When working with age, remember that you can often use Date-of-birth directly, without doing the age calculation. Don't forget that the smallest date refers to the oldest person.

Function CalcAge(datEmpDateOfBirth As Variant) As Integer
    CalcAge = Int(DateDiff("y", CDate(datEmpDateOfBirth), Date) / 365.25)
End Function

Assume that table1 contains field d1 with the birthdate information.
alter table table1 add d2 computed by (cast (floor(("TODAY"-d1)/365.25) as integer));
Note: floor is one of the functions that is supplied with the InterBase v5.x UDF library.