■ 기간을 구하는 방법을 보여준다.
▶ 기간 구하기 예제 (SQL)
|
1 2 3 |
EXECUTE dbo.GetDateDifference '2011-12-20', '2012-06-21'; |
▶ 기간 구하기 (SQL)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
CREATE PROCEDURE [dbo].[GetDateDifference] @P_StartDate DATETIME, -- 시작일자 @P_EndDate DATETIME -- 종료일자 AS BEGIN DECLARE @YearCount INT; DECLARE @MonthCount INT; DECLARE @DayCount INT; DECLARE @TemporaryDate DATETIME; IF @P_StartDate > @P_EndDate BEGIN SET @TemporaryDate = @P_EndDate; SET @P_EndDate = @P_StartDate; SET @P_StartDate = @TemporaryDate; END SET @YearCount = YEAR (@P_EndDate) - YEAR (@P_StartDate); SET @MonthCount = MONTH(@P_EndDate) - MONTH(@P_StartDate); SET @DayCount = DAY (@P_EndDate) - DAY (@P_StartDate); IF @DayCount < 1 BEGIN SET @MonthCount = @MonthCount - 1; SET @DayCount = @DayCount + DATEDIFF(D, DATEADD(M, -1, @P_EndDate), @P_EndDate); END IF @MonthCount < 1 BEGIN SET @YearCount = @YearCount - 1; SET @MonthCount = @MonthCount + 12; END DECLARE @Difference VARCHAR(20); SET @Difference = ''; IF @YearCount <> 0 BEGIN SELECT @Difference = @Difference + CONVERT(VARCHAR(3), @YearCount) + '년'; END IF @MonthCount <> 0 BEGIN SELECT @Difference = @Difference + CONVERT(VARCHAR(3), @MonthCount) + '월'; END IF @DayCount <> 0 BEGIN SELECT @Difference = @Difference + CONVERT(VARCHAR(3), @DayCount) + '일'; END SELECT @Difference AS [Difference]; END GO |