CODEWITHSUNDEEP
sqlsql-server-2005
ratty
ratty

Reputation: 13444

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

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

Upvotes: 0

Views: 8820

Answers (3)

Pero P.
Pero P.

Reputation: 26992

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

Upvotes: 1

Pratik
Pratik

Reputation: 11745

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

Upvotes: 0

Mohamed Saligh
Mohamed Saligh

Reputation: 52

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;

Upvotes: 0

Related Questions