Get Years, Months and Days between Dates

@tomazsr,

Although, pastorchris's explanation of the datediff function and how it works is accurate, I see you are already using this in your code; therefore, you must be having difficulties with something else with the code, so will take a closer look.

I see now that the number of days is off.  This can be caused some times by leaps years and other such differences in number of days in a month.

Will let you know.

Kevin

Ok,

3 years, 6 months, and 7 days is correct.  

Using dateadd(year, 3, '20060228') puts you at 2009-02-28 00:00:00.000.

Using dateadd(month, 6, '20090228') puts you at 2009-08-28 00:00:00.000.

From 2009-08-28 00:00:00.000, you have the 29th, 30th, and 31st of August and then 4 additional days until September 4th.

Therefore, your calculations seem to be working fine, although, you can simplify this greatly to the code in the snippet.  Might even be more efficient way, but is what I quickly put together to test and determine what issue was.  The debug lines are unnecessary, but left in place to show technique for leaving yourself PRINT messages.

Hpoe that helped.

Regards,

Kevin

create procedure CalcYearsMonthsDays
@DateFrom datetime,
@DateTo datetime,
@Year int output,
@Month int output,
@Day int output
 
as
set nocount on
 
DECLARE @Date datetime, @TotalMonthsDiff int
SET @Year = 0
SET @Month	= 0
SET @Day	= 0
SET @Date	= @DateFrom
 
SET @TotalMonthsDiff = datediff(month, @DateFrom, @DateTo)
-- take advantage of integer division here to get # of years.
-- could wrap with floor() to be certain get just whole #.
SET @Year = @TotalMonthsDiff / 12 
-- use modulus division to get remainder of division by 12.
SET @Month = @TotalMonthsDiff % 12 
 
-- update running calculation date
SET @Date = dateadd(month, @TotalMonthsDiff, @Date)
 
--PRINT(@Date) -- debug
 
-- check we haven't gone too far
IF (@Date > @DateTo) 
BEGIN
   -- decrement date and months variable by 1
   SET @Date = dateadd(month, -1, @Date)
   SET @Month = @Month - 1
END
 
--PRINT(@Date) -- debug
 
SET @Day = datediff(day, @Date, @DateTo)
 
--SELECT @Year, @Month, @Day -- debug
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:

Thx Kevin,

I ll accept your solution.
I still didnt solve my problem :0)) because I didt start right. I m working on HRM application and one of te function
is that administrative insert working period of employee on some date.
So she insert that employee YX
has 3 years 6 months and 0 days working period on date 2009-08-31. On saving data
application must add years, months, days from 2009-08-31 until today, like current working period.
Here start my problems :0)) I take from 2009-08-31 3 years 6 months and 0 days and get 2006-02-28 and than I calc whole difference from 2006-02-28 to  2009-09-04 with procedure you so nice upgraded.
So I get 7 days but administrative want 4 days :0))

Thx a lot
Tomaz

So it appears you are really wanting to treat 02-28 as the end of the month instead of its actual date, can take a look at if thought I have in my head will work to fix issue.

Thx. I solving this problem two days now and I really need some different point of view :0))

This will do the following, if 3/1 then 3 years, 6 months, 3 days.  If 2/28, then will be 3 years, 6 months, 4 days.  If 3/2 will need to be from 3 years, 5 months, 4 days as will calculate from end of March.  Basically considering administrative to be starting from first of first full month including that day which is why I left 3/1 as I did.  If 3/1 should go to 4/1 then just remove the case statement.  If 3/1 should be same answer as 2/28 then make the offset for day 1 a 0.

Hopefully that is clear.

alter procedure CalcYearsMonthsDays
@DateFrom datetime,
@DateTo datetime,
@Year int output,
@Month int output,
@Day int output
 
as
set nocount on
 
DECLARE @Date datetime, @TotalMonthsDiff int, @Offset int
SET @Year = 0
SET @Month	= 0
SET @Day	= 0
SET @Offset = 
   case day(@DateFrom) 
      when 1 then 1
	  else datediff(dd, 
			@DateFrom, 
			dateadd(m, datediff(m, 0, @DateFrom)+1, 0)) 
      end
SET @Date	= @DateFrom + @Offset
 
SET @TotalMonthsDiff = datediff(month, @DateFrom, @DateTo)
-- take advantage of integer division here to get # of years.
-- could wrap with floor() to be certain get just whole #.
SET @Year = @TotalMonthsDiff / 12 
-- use modulus division to get remainder of division by 12.
SET @Month = @TotalMonthsDiff % 12 
 
-- update running calculation date
SET @Date = dateadd(month, @TotalMonthsDiff, @Date)
 
PRINT(@Date) -- debug
 
-- check we haven't gone too far
IF (@Date > @DateTo) 
BEGIN
   -- decrement date and months variable by 1
   SET @Date = dateadd(month, -1, @Date)
   SET @Month = @Month - 1
END
 
PRINT(@Date) -- debug
 
SET @Day = datediff(day, @Date, @DateTo + 1) 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:

Im very thanksfull for your help.... but :0))
problem is that she could insert any years, months, days on any chosen date. If I try
with 3 years, 6 months, and 15 days on date 2009-08-31 then result is the same 4 days
like in the first example.

Not sure if I am fully understanding, but if the administrator is giving you the number of years and months and days in the past up front with a date of 2009-08-31, then you are doing reverse calculation to get the start date. Altering my last suggestion, you get code in the snippet.

Went through this process to emulate what I understand and to test results:

declare @days int,
@months int,
@years int,
@asOfDate datetime,
@fromDate datetime,
@toDate datetime

set @years = 3
set @months = 6
set @days = 15
set @toDate = '20090904'
set @asOfDate = '20090831'

set @fromDate =
dateadd(month,
-(@years * 12 + @months),
dateadd(day, -@days, @asOfDate)
)
;

select @asOfDate as AsOfDate,
@years as yearsInput,
@months as monthsInput,
@days as daysInput,
@fromDate as CalculatedStartDate,
@toDate as DesiredEndDate


execute dbo.CalcYearsMonthsDays @fromDate, @toDate,
@years output, @months output, @days output

select @years as years,
@months as months,
@days as days

Get 3 years, 6 months, and 19 days with last scenario.

Still get 3 years, 6 months, and 4 days with the other.

M-1

alter procedure CalcYearsMonthsDays
@DateFromIn datetime,
@DateToIn datetime,
@Year int output,
@Month int output,
@Day int output
 
as
set nocount on
 
DECLARE @Date datetime, 
   @DateFrom datetime,
   @DateTo datetime,
   @TotalMonthsDiff int, 
   @Offset int
 
SET @Year = 0
SET @Month	= 0
SET @Day	= 0
SET @DateFrom = @DateFromIn + 1
SET @DateTo = @DateToIn + 1
SET @Date = @DateFrom
 
SET @TotalMonthsDiff = datediff(month, @DateFrom, @DateTo)
-- take advantage of integer division here to get # of years.
-- could wrap with floor() to be certain get just whole #.
SET @Year = @TotalMonthsDiff / 12 
-- use modulus division to get remainder of division by 12.
SET @Month = @TotalMonthsDiff % 12 
 
-- update running calculation date
SET @Date = dateadd(month, @TotalMonthsDiff, @Date)
 
--PRINT(@Date) -- debug
 
-- check we haven't gone too far
IF (@Date > @DateTo) 
BEGIN
   -- decrement date and months variable by 1
   SET @Date = dateadd(month, -1, @Date)
   SET @Month = @Month - 1
END
 
--PRINT(@Date) -- debug
 
SET @Day = datediff(day, @Date, @DateTo) 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:

This has been re-opened by the way since the accepted didn't solve your issue.  When we get this right, please accept the actual post that worked to make it easier for future readers. :)

Thx. I ll prepare detail description tomorow at work.

Think this is what you are after...

Days needs to be defined as days to end of month + days from start of month. The years / months are really a modulo function of the month datadiff between the start of one month through to the start of the other month...

Anyway, have a look below :

-- step 1 create the procedure (bit simplified - no outputs - but you can fix that
 
create procedure usp_CalcYearsMonthsDays @start datetime, @end datetime
as
Begin
  declare @starteom datetime
  declare @endsom datetime
  declare @year int
  declare @month int
  declare @day int
 
  set @starteom = DATEADD(month, DATEDIFF(month, -1, @start), -1)   -- the EOM for the start date
  set @endsom = DATEADD(month, DATEDIFF(month, 0, @end),0)          -- the SOM for the end date
 
  select @year  = datediff(mm,@starteom+1,@endsom) / 12, 
         @month = datediff(mm,@starteom+1,@endsom) % 12,
         @day   = datediff(d, @endsom , @end ) + datediff(d, @start, @starteom) + 1
 
  Select @year as [year], @month as [month], @day as [day]
end
go
 
-- now use the procedure...
 
exec usp_CalcYearsMonthsDays '20060228','20090904'
 
-- Results 
--  Year  Month  Day
--  3     6      4

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

I like it Mark.  Eliminates the need to subtract a month and handles the desired handling of end of month nicely.

Thanks mwvisa1, always enjoy sharing posts with you. You are indeed a gentleman and a scholar and represent everything good that is EE.

Hi,

I do some tests and looks ok if its date not this month.
If I insert on Date 2009-09-01 working period 3 years 6 months and 28 days I get
3 years 6 months and 34 days :0((

Sorry, not understanding where you are using the date...

If I do :

exec usp_CalcYearsMonthsDays '20060228','20090901'

I get  3years, 6months, 1day.

I ll try to explain better my problem .
DateFrom = SavedDate - saved years - saved months - saved days
Example: if employee have on 2009-09-01 3 years, 6 months, 28 days working period I get
DateFrom= dateadd(day,-WorkDays, dateadd(month,-WorkMonths,  dateadd(year,-WorkYears,SavedDate )))

DateTo=getdate() -- without time

and story:

When new employee start to work in company she or he bring from state office current work period
in years, months, days valid on some date. Our HRM administrator insert this data into program and from
now on program must calc working period automaticitly so DateTo is always Today.

And we went through all of this earlier tomazsr, and my solution solved that.  Did it not work?  Not sure if you were talking about mine or Mark's with respect to the calculation issue.

AsOfDate      yearsInput      monthsInput      daysInput      CalculatedStartDate      DesiredEndDate
9/1/2009 12:00:00 AM      3      6      28      2/4/2006 12:00:00 AM      9/4/2009 12:00:00 AM

My version:
years      months      days
3      7      0

Mark's:
year      month      day
3      6      28

Think the 3 years, 7 months is correct; however, liked Mark's approach so may want to see if we can merge the two.

declare @days int,
@months int,
@years int,
@asOfDate datetime,
@fromDate datetime,
@toDate datetime
 
set @years = 3
set @months = 6
set @days = 28
set @toDate = '20090904'
set @asOfDate = '20090901'
 
set @fromDate = 
dateadd(month, 
-(@years * 12 + @months), 
dateadd(day, -@days, @asOfDate)
)
;
 
select @asOfDate as AsOfDate, 
@years as yearsInput, 
@months as monthsInput, 
@days as daysInput,
@fromDate as CalculatedStartDate, 
@toDate as DesiredEndDate
 
 
execute dbo.CalcYearsMonthsDays @fromDate, @toDate, 
@years output, @months output, @days output
 
select @years as years, 
@months as months, 
@days as days
 
execute dbo.usp_CalcYearsMonthsDays @fromDate, @toDate

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

There is a problem if it is in the same month and year. Is that what you mean ?  
Need to check if day is before or after the end day because it should probably handle "anniversary" calculations differently. Can fix that...

But I am still a little confused...

mwvisa1 has already posted a solution for 3 years, 6 months and 28 days (per his comment above)...

in fact if you use your dateadd calculation, then the original requirement is not met either...

ie select dateadd(day,-4, dateadd(month,-6,  dateadd(year,-3,'20090907' )))  = 3/3/2006  not the 28th Feb 2006 - for that you need to subtract 7 days.

I try Mark's solution today and its not work in example when start day
is 2009-02-01 and end date is today. Result is 3 years 6 months and 34 days :0((

Nah, something else is wrong there - even with the "bug" of anniversary calcs, that should have happened.

And, my mistake above - used 20090707 not 20090704 as the original date...

Just fixing anniversary dates - will get back to you soon...

I think problem is when StartDateThisYearThisMonth is less than today date.
Like in example above must be 3 years 7 months and 6 days.

Here is my attempt at at updated version that takes into consideration both of our approaches that also returns the 3 years, 7 months, 0 days correctly for the above.  Note the offset still included and placement of the start of month usage.  With both combined a running date is unnecessary.

alter procedure CalcYearsMonthsDays
@DateFromIn datetime,
@DateToIn datetime,
@Year int output,
@Month int output,
@Day int output
 
as
set nocount on
 
DECLARE @DateFrom datetime,
   @DateFromSOM datetime,
   @DateTo datetime,
   @DateToSOM datetime,
   @TotalMonthsDiff int, 
   @Offset int
 
-- reset result values
SET @Year 	= 0
SET @Month      = 0
SET @Day        = 0
 
-- offset days by +1 for calculation accuracy
SET @DateFrom = @DateFromIn + 1
SET @DateTo = @DateToIn + 1
 -- calculate start of month values for dates
SET @DateFromSOM = dateadd(month, datediff(m, 0, @DateFrom), 0)
SET @DateToSOM = dateadd(month, datediff(m, 0, @DateTo), 0)
 
-- start caculations
SET @TotalMonthsDiff = datediff(month, @DateFrom, @DateToSOM)
-- take advantage of integer division here to get # of years.
-- could wrap with floor() to be certain get just whole #.
SET @Year = @TotalMonthsDiff / 12 
-- use modulus division to get remainder of division by 12.
SET @Month = @TotalMonthsDiff % 12 
 
SET @Day = 
   datediff(day, @DateToSOM, @DateTo) - 
      datediff(day, @DateFromSOM, @DateFrom) 
;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:

When running with today's date as the target:

years      months      days
3      7      3

This is essentially your 3 years, 6 months, 34 days as remember August had 31 days therefore, your original 28+3 (days to September 4th) give you another month, then the remaining 3 (days to September 7th) goes to days.

OK, so here is an updated version which checks if day anniversary date is less than or greater than day of "today" (end date)

declare @start datetime, @end datetime
set @start = '20060201'
set @end = '20090907'
 
  declare @year int
  declare @month int
  declare @day int
 
  declare @starteom datetime
  declare @startsom datetime
  declare @endsom datetime
 
  set @starteom = DATEADD(month, DATEDIFF(month, -1, @start), -1)
  set @startsom = DATEADD(month, DATEDIFF(month, 0, @start),0)
  set @endsom = DATEADD(month, DATEDIFF(month, 0, @end),0)
 
  if day(@start)<day(@end)
  begin
     set @month = datediff(mm,@startsom,@endsom) % 12
     set @year  = datediff(mm,@startsom,@endsom) / 12
     set @day = day(@end)-day(@start)
  end
  else
  begin
     set @month = datediff(mm,@starteom+1,@endsom) % 12 
     set @year  = datediff(mm,@starteom+1,@endsom) / 12
     set @day   = datediff(d, @endsom , @end ) + datediff(d, @start, @starteom) + 1
  end
 
  Select @day as [day], @month as [month], @year as [year]
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

Sorry mwvisa1, didn't see your posting... and a few of the other comments...

I am using dates '20060201' and '20090907' and get 6 days, 7 months, 3 years.  What dates are you using ?

Nice updates, Mark.  Note that that was a major part of the difference in versions previously.  Your approach eliminated the necessary check on date in an if statement.  You will see that in my post here that already worked -- http:#25261644.

This post contains the test script I am using since the application here is that the HR users are adding user on a specific date with a pre-determined number of years, months, and days of service.

My adjustment here uses your shorter approach but with my offset methodology and was able to get it to work without the if -- http:#25275220.

Like your last post too, although I haven't tested yet; however, you can use the test code I linked to for yourself.  Cases that should work is 3 years, 6 months, 0 days on 8/31/2009 should be 3 years, 6 month, 7 days when date to is set to today.  3 years, 6 months, 28 days on 9/1/2009 should be 3 years, 7 months, 3 days to today.  3 years, 6 months, 15 days on 8/31/2009 should be 3 years, 6 months, 22 days.

mwvisa1: I think you do need to conditionally check - my original was hoping to get around that, but overlooked one small very important point...

Try yours with :

  declare @start datetime
  declare @end datetime
  declare @year int
  declare @month int
  declare @day int

  set @start = '20060228'
  set @end = '20090930'               -- by inspection should be 3 years, 7 months and 2 days...

  exec CalcYearsMonthsDays @start, @end, @year output, @month output, @day output

  Select @day as [day], @month as [month], @year as [year]

  select dateadd(year,-@year, dateadd(month,-@month, dateadd(day,-@day,@end )))

No, Mark.  The point is it is NOT based on the actual chronological difference in time.  It is based on duration of service a user comes in with and 2/28 is end of month so wants treated as such and not the 28th.  So should probably come out something like 3 years 7 months; however, without knowledge of the inputs it is not really an apples and apples comparison.

After you test my scripte, try putting in 3 years, 7 months, and 0 days as the inputs as of date being 20090930.  Since we are starting with 3 years, 7 months and 0 days on September 30th, then calculating forward to 20090930 you should get same answer.  Check mine, you will see it does.  

Replace execute statement to use usp_CalcYearsMonthsDays (yours) and you will see you get 3 years, 7 months, and 2 days.  This can't be since we explicitly said we have entered with 3, 7, and 0.

As you will see at the top of the thread that was the exact dilemma we started this journey with and why I re-opened the question to get to my new solution as my originally accepted one did what you are stating that is correct when threating the dates independent of what the system is if you are just strictly going based on what day of month it is.  

For this system though, end of month or beginning of the month are of importance, so 2/28 is end of month and so when compared to end of month of any other month the days difference is 0.

I read the question as very simply as the difference in number of years, months and days.

To me that means:

"20060228" to "20090904" is 3 years, 6 months, 4 days
"20060220" to "20090930" is 3 years, 7 months, 10 days
"20090201" to "20090908" is 0 years, 7 months, 7 days
"20090221" to "20090331" is 0 years, 1 month, 10 days

There is still a small bug in my routine (I think) in so much as it probably does need to check the fromdate for calendar eom and ignore the from days if the todate has more days in it.

But see what your routine gets using the above dates...

Mark, my original answer here http:#25261644, given 3 days ago, gets the right response as shown above.  I will have to check the one that I added your methodology too as I don't know it as well.  Was just trying to give value to your suggestion.  I really already went through all this discovery and solved issue. ;)

Think I will stick with my original suggestion as to not get too off track!  You are right, the new version carried through flaw from original post made on the end of month methodology.  I am sure whatever you come up now works -- just done duplicating my original effort.  Have fun!

Hi ,

I test mwvisa1 last code and something is still not ok:

exec [dbo].[CalcYearsMonthsDays]
'20090223',
'20090908',
@Year  output,
@Month  output,
@Day  output

Output is:
3
7
-15

And same data with mark's code:

exec [dbo].[CalcYearsMonthsDays2]
'20060223',
'20090908',
@Year  output,
@Month  output,
@Day  output

Output:
3
6
13

its ok.
I ll do some more test with border data

Did you ever check my code here -- http:#25261644?
Just seems like we lost track of that when last comment was I will give that a try.  Spent last three days re-working Mark's because that is what you responded with on error.  Anyway, just curious...but use Mark's latest code.  Only issue I had was we went full circle trying to avoid IF statement I already gave you only to end up back at code using IF statement.  May as well have stayed with what we had.

I dont know if I test it. You and Mark add so many posts. :0)) so I test last one from you and last one from Mark today. I ll test it.

You are fine.  It was my fault for jumping into to that.  If Mark's last post works, I suggest you use that.  No need to waste more of your time.  I think that is a great function -- enjoy!

Calc is now in HRM department testing :0)) so if thay ll be happy I ll be happy to :0))