Find Employee Experience

Transact-SQL
1

Hi,

How to find employee experience. for example. employee Joined date 2012-Aug-18. Now, Employee experience should be display as 3 Years, 10 Months, 6 Days.

Pls help.

Regards
SP.Ajay Pal

2

You should be doing this formatting at the application level:[code]declare @Employees table (
JoinDate date
)

insert into @Employees(JoinDate)
values ('20120818')

;with EmpYears
as (
select
DateDiff(year, JoinDate, GetDate()) -
case
when DateAdd(Year, DateDiff(year, JoinDate, GetDate()), JoinDate) > GetDate()
then 1
else 0
end Years,
JoinDate BaseDate
from
@Employees
),
EmpYears2
as (
select
Years,
DateAdd(Year, Years, BaseDate) BaseDate
from
EmpYears
),
EmpMonths
as (
select
Years,
DateDiff(Month, BaseDate, GetDate()) -
case
when DateAdd(Month, DateDiff(Month, BaseDate, GetDate()), BaseDate) > GetDate()
then 1
else 0
end Months,
BaseDate
from
EmpYears2
),
EmpMonths2
as (
select
Years,
Months,
DateAdd(Month, Months, BaseDate) BaseDate
from
EmpMonths
),
EmpDays
as (
select
Years,
Months,
DateDiff(Day, BaseDate, GetDate()) -
case
when DateAdd(Day, DateDiff(Day, BaseDate, GetDate()), BaseDate) > GetDate()
then 1
else 0
end Days
from
EmpMonths2
)
select
cast(Years as varchar(4)) + ' Years, ' + cast(Months as varchar(2)) + ' Months, ' + cast(Days as varchar(2)) + ' Days.'
from
EmpDays[/code]