[MSSQL] 計算年資SQL(N年N月)

之前幫HR計算員工年資(N年N月)，因此撰寫了此SQL，在此簡單紀錄一下。

年資計算SQL

CREATE FUNCTION [dbo].[FN_CalculateDateDuration] (@startDate SMALLDATETIME, @endDate SMALLDATETIME)
RETURNS @retDateDuration TABLE
(
    DurationYear  INT  NULL,
    DurationMonth INT  NULL
)
AS
BEGIN

    DECLARE @yearDiff INT
    DECLARE @monthDiff INT

    -- 計算年資
    SELECT @yearDiff = DATEDIFF(YEAR, @startDate, @endDate)
    SELECT @monthDiff = DATEDIFF(MONTH, @startDate, @endDate)

    -- 未滿年資修正
    IF( DATEDIFF(DAY, DATEADD(YEAR, @yearDiff, @startDate), @endDate) < 0 )
        SET @yearDiff = @yearDiff - 1

    IF( DATEDIFF(DAY, DATEADD(MONTH, @monthDiff, @startDate), @endDate) < 0 )
        SET @monthDiff = @monthDiff -1

    -- 扣除年資後的月數
    SET @monthDiff = @monthDiff - 12 * @yearDiff

    -- 回傳年資(年,月)
    INSERT @retDateDuration( DurationYear, DurationMonth )
    SELECT IIF( @yearDiff < 0, 0, @yearDiff ), IIF( @yearDiff < 0 OR @monthDiff < 0, 0, @monthDiff )

    RETURN
END

如有錯誤或建議，歡迎留言指教，謝謝!!
(相關內容如有侵犯隱私或著作權，請協助通知刪除，感謝)