Fraction of Month
I need to get "Tenure" of employees based on startDate and EndDate
In my attached select it returns whole months.
start 2011-06-27 and end 2011-12-14 would be 5 whole months but 6 is returned
start 2008-06-12 and end 2009-03-26 correctly returns 9 months
Little help?
In my attached select it returns whole months.
start 2011-06-27 and end 2011-12-14 would be 5 whole months but 6 is returned
start 2008-06-12 and end 2009-03-26 correctly returns 9 months
Little help?
Select @temure = datediff(mm,u.employmentStart,IsNull(u.employmentEnd,getdate()))
The reason for this is that datediff (in SQL Server, VBA, and Access) does NOT calculate elapsed time. Rather, it returns how many time period boundaries there are between two dates/times.
For example:
DATEDIFF(year, '2011-01-01', '2012-12-31 23:59:59')
DATEDIFF(year, '2011-12-31 23:59:59', '2012-01-01')
both return the same value, 1 (year), even though in the first case very nealy two years have elapsed, and in the second case only one second has elapsed. That said, for both cases, a single year boundary is crossed when moving from the start value to the end value.
When the time interval is seconds or milliseconds, you may as well treat the result as "elapsed time", but for any interval longer than that, remember this warning :)
For example:
DATEDIFF(year, '2011-01-01', '2012-12-31 23:59:59')
DATEDIFF(year, '2011-12-31 23:59:59', '2012-01-01')
both return the same value, 1 (year), even though in the first case very nealy two years have elapsed, and in the second case only one second has elapsed. That said, for both cases, a single year boundary is crossed when moving from the start value to the end value.
When the time interval is seconds or milliseconds, you may as well treat the result as "elapsed time", but for any interval longer than that, remember this warning :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
best way to do this, get the difference in seconds then convert them into months
As per my understanding please try the below
Select case when DAY('2011-06-27') > Day('2011-12-14') then datediff(mm,'2011-06-27',I
datediff(mm,'2011-06-27',I
Select case when DAY('2011-06-27') > Day('2011-12-14') then datediff(mm,'2011-06-27',I
datediff(mm,'2011-06-27',I
ASKER
Worked. Thanks
sureshbabukrish,
That is not going to work, seeing as how months have differing numbers of days.
Patrick
>>best way to do this, get the difference in seconds then convert them into months
That is not going to work, seeing as how months have differing numbers of days.
Patrick
ASKER
That's why I went with s___k: answer.
Loaded and actually looked at the data for about 100 instances in a 10000 row table and they were all correct.
If anything wierd shows up I'll handle at that time
Loaded and actually looked at the data for about 100 instances in a 10000 row table and they were all correct.
If anything wierd shows up I'll handle at that time
@tenure=select datediff(m, u.employmentStar, u.employmentEnd) -
case when datepart(d, u.employmentStar) > datepart(d, u.employmentEnd) then 1 else 0
end