Reputation: 65
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
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