I am stuck with my query. I have a table called Patient. In this table a column has patient DOB. Actually I want to display the exact age of the patient.

For example:

PatName   DOB (MM/dd/yyyy)   Age
a         06/02/1947         65 Years 1 Month/s 3 Days
b         07/10/1947         64 Years 11 Month/s 25 Days
c         -----------        -----------------------

I want to display the age of the above format.

I have already googled about this but nobody helped.

If you have any query regarding this pls post that.

Thanks

Hi Check the query below.

--DROP TABLE patient
CREATE TABLE patient(PatName varchar(100),DOB date,  Age varchar(100))
INSERT INTO patient
VALUES('a','06/02/1947',NULL),('b','07/10/1947',NULL),('c','12/21/1982',NULL)

;WITH CTE(PatName,DOB,years,months,days) AS
(SELECT PatName,DOB,DATEDIFF(yy,DOB,getdate()),DATEDIFF(mm,DOB,getdate()),DATEDIFF(dd,DOB,getdate()) FROM patient)

--SELECT * FROM CTE

SELECT PatName,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

more