SQL SERVER (MSSQL) 날짜와 날짜 사이 모든 날짜 구하기

쿼리만 보기

아래에는 내가 겪은 문제와 해결과정을 잡다하게 써놨다. 그런데 검색을 통해 들어온 사람들은 쿼리만 빨리 보고싶을 것 같아서 작성한 쿼리를 맨 위에 올려둔다.

아래의 쿼리를 실행시키면 바로 결과가 나올 것이다.

declare @st_search datetime='2022-12-01'
declare @ed_search datetime='2023-02-28'

;WITH Nums AS
(
  SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_columns
)

SELECT
    HIST_YY,
    HIST_MM,
    HIST_DAY,
    concat(hist_yy, '-', RIGHT('0' + CAST(HIST_MM AS VARCHAR(2)), 2), '-',  RIGHT('0' + CAST(HIST_DAY AS VARCHAR(2)), 2)) concat_
FROM
    (
    SELECT LEFT(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 4) [HIST_YY]
            , CAST(CAST(SUBSTRING(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 5, 2) AS INT) AS VARCHAR) [HIST_MM]
            , CAST(CAST(RIGHT(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 2) AS INT) AS VARCHAR) [HIST_DAY]
    FROM
        Nums
    WHERE
        n BETWEEN 1 AND CONVERT(INT, @ed_search - @st_search) + 1
    ) A
GROUP BY
    HIST_YY, HIST_MM, HIST_DAY;

직면한 문제와 해결방법

MSSQL 을 사용하면서 간혹 테이블에 날짜가 연, 월, 일 별로 나눠진 곳이 있었다. 등록일자나 수정일자가 따로 있긴 했지만, 인덱싱이 연, 월, 일에만 걸린 경우가 있어 이럴 때 특정 기간별로 (이를테면 A 일자부터 B 일자까지) 날짜 검색을 하기에 굉장히 애매했다.

2022-04-25 ~ 2022-05-01 과 같은 형식으로 날짜를 입력하면, 2022-04-25, 2022-04-26, 2022-04-27, 2022-04-28, 2022-04-29, 2022-04-30, 2022-05-01 과 같은 형식으로 날짜를 추출해주는 기능이 필요해서 검색을 해보니 다양한 방식으로 만들어낼 수 있었다.

연, 월, 일과 같은 날짜는 특성이 있다. 이를테면 4월은 30일까지밖에 존재하지 않고, 5월은 31일까지 존재한다. 이러한 특성이 단순한 계산으로는 답을 구할 수 없게 만든다. 어떻게든 IF 문을 적용해야 한다.

개념적 해결 방법

단순한 더하기 빼기로 날짜 계산이 가능하게 만들어야 한다. 그러나, 의외로 조건문은 필요 없다.

컴퓨터 시스템 내부에서 이미 제공하는 값을 이용해보자. 컴퓨터 시스템에서는 보통 날짜를 표현하기 위해 Unix time 이라는 것을 사용한다. 유닉스 시간은 1970-01-01 를 0 이라는 기준으로 둔다. 해당 기준에서 몇초가 지났는지를 이용하여 현재 시간을 계산한다.

이를테면 내가 글을 쓰는 2022년 6월 21일 10시 41분은 유닉스 시간으로는 아래와 같이 표현된다.

Current Unix time
1655772412 (update)
(2022-06-21T00:46:52+00:00)

위에 나온 숫자인 1655772412 를 계산기를 이용해 60 으로 나눠서 분단위로 바꾸고 다시 60 으로 나누어 시간 단위로 바꾸고, 다시 24 로 나누어 일 단위로 바꾸고 다시 365 로 나누어 연 단위로 바꿔보자.

그럼 대략 52.5 가 나온다. 이를 1970 에 더하면 놀랍게도 현재의 연도인 2022 가 나오게 된다.

이제 단순한 더하기 빼기로 특정 시점의 날짜를 구할 수 있다는 것을 알았으니, SQL 문에 적용해보자.

해결 사례 1: 시스템 컬럼 이용하기

declare @st_search datetime='2022-04-29'
declare @ed_search datetime='2022-05-06'

;WITH Nums AS
(
  SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_columns
)

SELECT
    HIST_YY,
    HIST_MM,
    HIST_DAY
FROM
    (
    SELECT LEFT(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 4) [HIST_YY]
            , CAST(CAST(SUBSTRING(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 5, 2) AS INT) AS VARCHAR) [HIST_MM]
            , CAST(CAST(RIGHT(CONVERT(VARCHAR,  @st_search - 1 + n, 112), 2) AS INT) AS VARCHAR) [HIST_DAY]
    FROM
        Nums
    WHERE
        n BETWEEN 1 AND CONVERT(INT, @ed_search - @st_search) + 1
    ) A
GROUP BY
    HIST_YY, HIST_MM, HIST_DAY;

시스템 컬럼에 이미 들어있는 값을 이용하여 반복을 돌리는 방식으로 사이 날짜를 구해본 것이다.

• MSSQL 에서 datetime 으로 선언된 날짜에 1 을 더하면, 자동으로 다음 날짜가 구해진다.
• datetime 타입을 INT 형태로 형변환하여 빼주면 날짜가 며칠차이가 나는지 알 수 있다.
  • 이건 DATEDIFF 메서드를 이용해 하는 편이 더 가독성이 좋다.
  • DATEDIFF(d, @st_search, @ed_search) 는 같은 역할을 한다.
• MSSQL 반복문 꼼수를 통해 날짜 차이만큼 반복하면 된다.
  • MSSQL 에서 반복 돌리는 방법

datetime 에 +1 을 하는 순간 아마 유닉스 시간을 기준으로 86400 을 내부적으로 더해준다고 추측할 수 있다.

조금 더 깔끔하게

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

그런데 여기서는 YYYY, MM, DD 가 각각 나누어져 있지는 않기 때문에 나누는 수고를 조금 더 해야한다.

해결 사례 2: 미리 만들어둔 Calendar 테이블 이용하기

SET DATEFIRST 7, LANGUAGE '한국어';
DECLARE @StartDate date = '20200101',
        @Years     int  = 1;
;WITH seq(n) AS
(
  SELECT 1 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, DATEADD(YEAR, @Years, @StartDate))
),
d(d) AS
(
  SELECT DATEADD(DAY, n - 1, @StartDate) FROM seq
),
src AS
(
  SELECT
    TheDate         = CONVERT(date,       d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = DATEPART(QUARTER,   d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
dim AS
(
  SELECT
    TheDate,
    TheDay,
    TheDaySuffix        = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE
                            CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
                            WHEN '3' THEN 'rd' ELSE 'th' END END),
    TheDayName,
    TheDayOfWeek,
    TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER
                            (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    IsWeekend           = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST
                            WHEN 1 THEN 6 WHEN 7 THEN 1 END,7)
                            THEN 1 ELSE 0 END,
    TheWeek,
    TheISOweek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheISOYear          = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1
                            WHEN TheMonth = 12 AND TheISOWeek = 1  THEN -1 ELSE 0 END,
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheLastOfYear,
    IsLeapYear          = CONVERT(bit, CASE WHEN (TheYear % 400 = 0)
                            OR (TheYear % 4 = 0 AND TheYear % 100 <> 0)
                            THEN 1 ELSE 0 END),
    Has53Weeks          = CASE WHEN DATEPART(WEEK,     TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    Has53ISOWeeks       = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                          + CONVERT(char(4), TheYear),
    Style101            = CONVERT(char(10), TheDate, 101),
    Style103            = CONVERT(char(10), TheDate, 103),
    Style112            = CONVERT(char(8),  TheDate, 112),
    Style120            = CONVERT(char(10), TheDate, 120)
  FROM src
)
SELECT *
  INTO #temp_calendar
  FROM dim
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);
GO
ALTER TABLE #temp_calendar ALTER COLUMN TheDate date NOT NULL;
GO
ALTER TABLE #temp_calendar ADD CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED(TheDate);
GO

• 위와 같이 미리 만들어둔 달력 테이블을 이용하면 된다.
• style 112 의 경우, 20220621, 20220622 와 같은 형식으로 날짜가 저장되기 때문에 수학적 대소비교로도 특정 기간을 구할 수 있게 된다.