find age in year from date of birth from current date using sql query in sql server2005?

find age i开发者_运维百科n year from date of birth from current date using sql query?

You may want to refer to a well known solution developed by Steve Kass which accounts for a number of edge cases including leap years. Here it is for reference:

DECLARE @birthdate SMALLDATETIME, @endDate SMALLDATETIME 
SET @birthdate = '19791204' 
SET @endDate = GETDATE() 
 
SELECT DATEDIFF 
( 
    YEAR, 
    @birthdate, 
    @endDate 
) - CASE 
    WHEN 100 * MONTH(@endDate) + DAY(@endDate) 
    < 100 * MONTH(@birthdate) + DAY(@birthdate) 
    THEN 1 ELSE 0 END 

Drop table Bird;

CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);



INSERT INTO  Bird VALUES ('BlueBird','Joe','Car','f','1999-03-30',NULL);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1979-04-30',NULL);
INSERT INTO  Bird VALUES ('RedBird','Yin','Bus','m','1998-01-30',NULL);



SELECT name, birth, CURDATE(),
       (YEAR(CURDATE())-YEAR(birth))
        - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
       AS age
       FROM Bird;

DECLARE
@dob DATETIME
BEGIN
SET @dob = '19791204' -- set your birthday in datetime..
SELECT DATEDIFF(YEAR,@dob,GETDATE()) AS AGE
-- if you do it reverse :  DATEDIFF(YEAR,GETDATE(),@dob,) you will get negative
-- refer : http://msdn.microsoft.com/en-us/library/ms189794.aspx
END