0

I want to write a query in sql for find the age of a person in day month and year .....lik3 Age:'65Years/7Month/2Days'

I have a table consist the name and the date of birth of employee

I am able to find the age in year or month or day by using datediff query but donno how to write for mixup of all

DATEDIFF(day,Column_1,DateNow())
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
ashi
  • 108
  • 11
  • possible duplicate of [How to calculate age in T-SQL with years, months, and days](http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days) – tom redfern Jul 27 '12 at 09:01

3 Answers3

2

try this:

CREATE TABLE emp(ename varchar(100),DOB date,  Age varchar(100))
INSERT INTO emp
VALUES('d','06/02/2011',NULL)--,('b','07/10/1947',NULL),('c','12/21/1982',NULL)



;WITH CTE(ename,DOB,years,months,days) 
AS 
(
SELECT 
    ename,DOB,DATEDIFF(yy,DOB,getdate()),DATEDIFF(mm,DOB,getdate()),DATEDIFF(dd,DOB,getdate()) 
FROM 
    emp
)
SELECT 
    ename,DOB,CAST(months/12 as varchar(5))+' Years'+
    CAST((months % 12) as varchar(5))+' month/s '+
    CAST(CASE WHEN DATEADD(MM,(months % 12),DATEADD(YY,(months/12),DOB)) <= GETDATE() then 
            DATEDIFF(dd,DATEADD(MM,(months % 12),DATEADD(YY,(months/12),DOB)),GETDATE()) 
        ELSE DAY(getdate())   
        END 
        as varchar(5))+' days' as Age
FROM CTE
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
0

Try here http://social.msdn.microsoft.com/Forums/en/transactsql/thread/399a7e87-bc78-47a5-8cc2-2d2436886fd7 google is your friend "ms sql for age from date"

Ian P
  • 1,724
  • 1
  • 10
  • 12
0

See the method shown here http://www.sqlservercurry.com/2010/07/calculate-age-from-date-of-birth-using.html

Madhivanan
  • 13,470
  • 1
  • 24
  • 29