how to calculate employee lenght of service

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to calculate employee lenght of service

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2010-04-01 : 08:07:03

Hi,

i want to calculate employee length of services from his joining date. i want to show services length like 05 Years 06 Moth 21 Days.

kindly tell how i can calculate this.

thx

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-01 : 08:47:55

Hi,

You can write a udf and pass the date to the function.
Sample function:

CREATE FUNCTION dbo.GetDuration(@FromDt as datetime)
returns varchar(255)
as
BEGIN
DECLARE @YRS AS SMALLINT, @MNT AS SMALLINT, @DY AS SMALLINT, @Fvalue varchar(255)

SET @YRS = DATEDIFF(YY, @FromDt,GETDATE())
SET @MNT = DATEDIFF(MM, @FromDt,GETDATE()) % 12

SET @DY = DATEDIFF(DD,GETDATE(), DATEADD(MM, (@YRS * 12) + @MNT,@FromDt) )

SET @Fvalue = CAST(@YRS AS VARCHAR) + ' YEARS ' + CAST(@MNT AS VARCHAR) + ' MONTH ' + CAST(@DY AS VARCHAR) + ' DAYS '

RETURN @Fvalue

END
GO

Due to time constraint i didn't carried out the testing of the UDF so it may be possible that it may not be 100% perfect.

Do the testing and use it.

Another way:

--Again due to time factor did only for years and months..

DECLARE @TEST TABLE
(ENO INT ,
ENAME VARCHAR(50),
DOJ DATETIME)

INSERT INTO @TEST
SELECT 1,'XXX','2001-01-05' UNION
SELECT 2,'YYY','1995-01-03' UNION
SELECT 3,'ZZZ','2001-01-05' UNION
SELECT 4,'AAA','2001-01-05'

SELECT *, CAST(DATEDIFF(YY, DOJ,GETDATE()) AS VARCHAR ) + ' YEARS ' + CAST(DATEDIFF(MM, DOJ,GETDATE()) % 12 AS VARCHAR) + ' MONTHS '
FROM @TEST

Regards,
Bohra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources