CODEWITHSUNDEEP
sql-servert-sql
Ravindra Bisht
Ravindra Bisht

Reputation: 65

Age Calculator in SQL

I have following sQL query, which is showing correct result as "28 Year, 1 Month, 16 Day old".

But, I want, it shows display as in years,months,days (in plural) if their number is more than 1.

For example- In above result, year is greater than 1, then it shows 28 Years.

Declare @DOB datetime,@TempDate datetime,@Year int,@Month int,@Day int
Set @DOB='09/13/1991'
Set @TempDate=@DOB

Select @Year=DateDiff(Year,@TempDate,getdate())-
                Case
                When (Month(@TempDate)>Month(getdate()) or Month(@TempDate)=Month(getdate()) AND Day(@TempDate)>Day(getdate()))
                Then 1
                Else 0
                End

Select @TempDate=DateAdd(YEAR,@Year,@DOB)
Select @Month=DateDiff(Month,@TempDate,getdate())-
                Case
                When (Month(@TempDate)>Month(getdate()) OR Day(@TempDate)>Day(getdate()))
                Then 1
                Else 0
                End

Select @TempDate=DateAdd(Month,@Month,@TempDate)

Select @Day=DateDiff(Day,@TempDate,getdate())

Select Cast(@Year as nvarchar(2))+ ' Year, '+Cast(@Month as nvarchar(2))+ ' Month, '+Cast(@Day as nvarchar(2))+' Day old'

Upvotes: 2

Views: 152

Answers (1)

droebi
droebi

Reputation: 944

For example try this:

SELECT CAST(@Year AS NVARCHAR(2)) + IIF(@Year > 1,' Years, ','Year, ') + CAST(@Month AS NVARCHAR(2)) + IIF(@Month > 1,' Months, ',' Month, ') + CAST(@Day AS NVARCHAR(2)) + IIF(@Day > 1,' Days ',' Day ') + 'old'

Upvotes: 5

Related Questions