计算工龄函数，就是从到职日期到当前有多少年多少月多少天：
/*
功能：統計工齡，格式: n年n月n日
設計：ok_008
日期：2008-01
備注：
*/
CREATE FUNCTION SumWorkAge (@OnDutyTime datetime)
RETURNS nvarchar(50) AS
BEGIN
DECLARE @Today datetime
SELECT @Today=Today FROM vwgToday
RETURN
CAST((CASE WHEN DAY(@OnDutyTime)<=DAY(@Today) THEN 0 ELSE -1 END +DATEDIFF(Month,@OnDutyTime,@Today))/12 AS nvarchar(2)) +'年'
+RIGHT('0'+CAST((CASE WHEN DAY(@OnDutyTime)<=DAY(@Today) THEN 0 ELSE -1 END +DATEDIFF(Month,@OnDutyTime,@Today))%12 AS nvarchar(2)),2) +'月'
+RIGHT('0'+CAST(CASE WHEN DAY(@OnDutyTime)<=DAY(@Today) THEN DAY(@Today)-DAY(@OnDutyTime) ELSE
DATEDIFF(Day,@OnDutyTime,DATEADD(month,1,@OnDutyTime))-DAY(@OnDutyTime)+DAY(@Today) END AS nvarchar(20)),2) +'天'
END
@OnDutyTime参数是到职日期。
vwgToday是一个取当前日期的视图(因为在自定义函数中不能直接使用GETDATE())
视图vwgToday代码：
CREATE VIEW dbo.vwgToday
AS
SELECT GETDATE() AS Today