calculate duration in years months and days for each row

Question

I have a table where i store the date of player got enter in any game with the gameId and UserId .

Now i have to calculate how much time since each user playing game.

Table schema is as following

create table  GameUser

(
   UserId int identity(1,1) not null, 
   UserName nvarchar(500)

)

insert into GameUser SELECT 'rahul'
insert into GameUser SELECT 'yunus'
insert into GameUser SELECT 'nitin'


Create table GameEntry 
 (
    EntryId int identity(1,1) not null,
    GameId int , 
    UserId int , 
    EntryDate smalldatetime

 )


insert into GameEntry SELECT 1,1,'01/01/2009'
insert into GameEntry SELECT 1,2,'05/01/2009'
insert into GameEntry SELECT 1,3,'12/01/2009'
insert into GameEntry SELECT 2,1,'01/01/2010'
insert into GameEntry SELECT 2,3,'01/01/2013'

SQL FIDDELE with table scheme and test data

my result column for duration should be like this 2 years 2 months and 15 days or 3 months or 5 months 15 days or 9 days

I already check age calculation questions on SO, but there are more complicated .

Use `datediff(day, EntryDate, getdate())` to get the difference in days. After that you just have to do the calculation and formatting stuff. — Mikael Eriksson, Mar 04 '13 at 06:36
what about 30 or 31 or 28 days when i do manually calculation ? as my DOB is 01-01-1986 so i am today 27 years, 2 months, and 3 days old. http://easycalculation.com/date-day/age-calculator.php Thanks — rahularyansharma, Mar 04 '13 at 07:01
@rahularyansharma : I have two scenarios for link above : 1.Today's date :27/3/2013 & DOB : 28/2/2013 . Answer is : 0 years, 0 months, and 30 days. 2.Today's date :27/3/2013 & DOB : 28/2/2012 . Answer is : 1 years, 0 months, and 30 days. Calculation misses on extra day of the leap year. Do you want it to be the same way? — Ravi Singh, Mar 04 '13 at 08:08
no i don't want that , i want to calculate exact years,months and days . — rahularyansharma, Mar 04 '13 at 08:26
@rahularyansharma : Possible duplicate of : http://stackoverflow.com/questions/57599/how-to-calculate-age-in-t-sql-with-years-months-and-days and http://stackoverflow.com/questions/11345041/how-to-display-the-exact-age-in-year-month-day-format-in-sql-server and http://stackoverflow.com/questions/12588720/difference-between-dates-sql-server — Ravi Singh, Mar 04 '13 at 08:32

score 1 · Accepted Answer · answered Mar 06 '13 at 07:00

You can do something like this :

Declare @DOB DateTime
DECLARE @yy INT
DECLARE @mm INT
DECLARE @getdd INT
DECLARE @dd INT

SET @DOB='2011-10-11 00:00:00.000'

SET @yy = DATEDIFF(mm, @DOB, GETDATE())/12
SET @mm = DATEDIFF(mm, @DOB, GETDATE())%12 - 1
SET @dd = ABS(DATEDIFF(dd, DATEADD(mm,@mm , DATEADD(yy, @yy, @DOB)), GETDATE()))

SELECT Convert(varchar(10),@yy) + ' Years ' + Convert(varchar(10),@mm) + ' Months '  + Convert(varchar(10),@dd) + ' Days '

score -1 · Answer 2 · answered Mar 04 '13 at 06:13

-1

You can do something like this. Will return you the date in years, months and days.

SELECT CAST(DATEDIFF(yyyy, Max(EntryDate), GETDATE()) AS VARCHAR) + ' years, ' 
     + CAST(DATEDIFF(mm, Max(EntryDate), GETDATE()) AS VARCHAR) + ' months, ' 
     + CAST(DATEDIFF(dd, Max(EntryDate), GETDATE()) AS VARCHAR) + ' days', 
     GE.userId
FROM
    GameEntry AS GE
GROUP BY
    GE.userId

answered Mar 04 '13 at 06:13

Praveen Nambiar

4,852
1
22
31

2 years 2 months and 15 days or 3 months or 5 months 15 days or 9 days have you check this in my question ? – rahularyansharma Mar 04 '13 at 06:18
Ohh well...considering the Subject of your question i thot this is what u wanted. – Praveen Nambiar Mar 04 '13 at 06:45

calculate duration in years months and days for each row

2 Answers2