tomazsr
asked on
Get Years, Months and Days between Dates
Hi,
I need to get exact difference between two dates in years, months, days.
I try few ideas from internet but its not ok, days are not calc right.
Please for any idea
Tomaz
If I run procedure :
declare @Year int ,
@Month int ,
@Day int
exec CalcYearsMonthsDays
'20060228',
'20090904',
@Year output,
@Month output,
@Day output
print @Year
print @Month
print @Day
I get:
3 years
6 months
7 days
but must be 4 days :0((
I need to get exact difference between two dates in years, months, days.
I try few ideas from internet but its not ok, days are not calc right.
Please for any idea
Tomaz
If I run procedure :
declare @Year int ,
@Month int ,
@Day int
exec CalcYearsMonthsDays
'20060228',
'20090904',
@Year output,
@Month output,
@Day output
print @Year
print @Month
print @Day
I get:
3 years
6 months
7 days
but must be 4 days :0((
create procedure CalcYearsMonthsDays
@DateFrom datetime,
@DateTo datetime,
@Year int output,
@Month int output,
@Day int output
as
set nocount on
DECLARE @Date datetime
SET @Year = 0
SET @Month = 0
SET @Day = 0
SET @Date = @DateFrom
--calculate the @Year
if(datepart(year,@DateTo)>datepart(year,@DateFrom))
begin
SET @Year = datediff(year,@DateFrom,@DateTo)
SET @Date = dateadd(year,@Year,@DateFrom)--update runningdate
if(@Date>@DateTo)
begin
SET @Year = datediff(year,@DateFrom,dateadd(year,-1,@DateTo)) --calculate years from @DateFrom to @DateTo - 1 year
SET @Date = dateadd(year,@Year,@DateFrom)--update runningdate
end
end
--add 1 month as long as running date is smaller than or equal to @DateTo
WHILE @Date<=@DateTo
begin
SET @Date = dateadd(month,1,@Date)
IF (@Date<=@DateTo)
begin
SET @Month=@Month+1
end
end
--set @Date back 1 month
SET @Date=dateadd(month,-1,@Date)
--start to count days
WHILE @Date<@DateTo
begin
SET @Date=dateadd(day,1,@Date)
SET @Day=@Day+1
end
@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
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
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
ASKER
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
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.
ASKER
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.
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)
ASKER
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.
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
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)
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. :)
ASKER
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 :
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
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.
ASKER
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((
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.
If I do :
exec usp_CalcYearsMonthsDays '20060228','20090901'
I get 3years, 6months, 1day.
ASKER
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,Sa vedDate )))
DateTo=getdate() -- without time
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,
DateTo=getdate() -- without time
ASKER
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.
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.
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
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'
ASKER
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((
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...
And, my mistake above - used 20090707 not 20090704 as the original date...
Just fixing anniversary dates - will get back to you soon...
ASKER
I think problem is when StartDateThisYearThisMonth is less than today date.
Like in example above must be 3 years 7 months and 6 days.
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)
;
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.
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]
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 ?
I am using dates '20060201' and '20090907' and get 6 days, 7 months, 3 years. What dates are you using ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 )))
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.
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...
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!
ASKER
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
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
ASKER
And same data with mark's code:
exec [dbo].[CalcYearsMonthsDays 2]
'20060223',
'20090908',
@Year output,
@Month output,
@Day output
Output:
3
6
13
its ok.
I ll do some more test with border data
exec [dbo].[CalcYearsMonthsDays
'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.
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.
ASKER
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!
ASKER
Calc is now in HRM department testing :0)) so if thay ll be happy I ll be happy to :0))
We'll keep our fingers crossed for you.
Good luck!
Good luck!
Usage is DATEDIFF ( datepart , startdate , enddate )
Select DATEDIFF ( dd, gatdate()-1, getdate()) returns 1, meaning 1 day's difference.
The datepart for year is yy. Please use this.
For you to get the different date parts, use boosk online.