IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

IWETHEY Home / IWETHEY Board / Databases Forum / Try this...
Post #1,931
by dpeterson
7/16/01 2:29:58 PM
Reply
New Try this...
I might be off, but I think this will work.

-- If the day and month are less than the current day and month, the real age
-- is one less than the difference of the years

DECLARE
@birthdate datetime,
@targetdate datetime,
@calc_age integer

SELECT
@birthdate = '1930-01-15',
@targetdate = '2001-01-15',
@calc_age = CONVERT(INT,DATEDIFF(yy, @birthdate, @targetdate))

SELECT
'Calculated Age' =
CASE
\tWHEN (day (@targetdate) >= day (@birthdate))
AND (month(@targetdate) >= month(@birthdate))
\t\tTHEN @calc_age
\t\tELSE @calc_age - 1
END
Darryl A. Peterson



I'm not as funny as I think I am.
Post #1,978
by ChrisR
7/16/01 8:36:22 PM
Reply
New Rearranged a bit...
For various reasons, I need to keep the computation together in a single query (the example uses @vars but the real code is grabbing a gazillion birthdays and computing the age). Anyhow, with a slight rearrangement, your code maps to something like the following:

DECLARE

   @birthdt DATETIME,
   @targetdt DATETIME

SELECT
   @birthdt = '1/15/1930',
   @targetdt = '12/31/2000'

SELECT
    age = CONVERT(INT, DATEDIFF(yy, @birthdt, @targetdt)) -
          (CASE WHEN (MONTH(@birthdt) = MONTH(@targetdt)) AND 
                     (DAY(@birthdt) > DAY(@targetdt)) 
                THEN 1 
                ELSE 0 
           END)


I'm still wondering, though, whether there's an easier way to do this?
     Calculating age in T-SQL - (ChrisR) - (2) - July 16, 2001, 01:43:03 PM EDT
         Try this... - (dpeterson) - (1) - July 16, 2001, 02:29:58 PM EDT
             Rearranged a bit... - (ChrisR) - July 16, 2001, 08:36:22 PM EDT

Powered by isospin!
33 ms