mssql 로 날짜 사이 기간 일자들 출력해 보기
토,일을 제외하고자 한다면, WHERE DATEWD NOT IN(1,7) 를 사용하시길 바랍니다.
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
SET @StartDateTime = '2019-05-01'
SET @EndDateTime = '2019-05-08';
WITH DateRange(Dates, DateWD) AS
(
SELECT @StartDateTime as Date, DATEPART(WEEKDAY, @StartDateTime)
UNION ALL
SELECT DATEADD(d,1,Dates), DATEPART(WEEKDAY, DATEADD(d,1,Dates))
FROM DateRange
WHERE Dates < @EndDateTime
)
SELECT Dates, DateWD
FROM DateRange
--WHERE DATEWD NOT IN(1,7) AND Dates NOT IN(
-- SELECT (HOLI_YEAR + '-' + HOLI_MONTH + '-' + HOLI_DAY) AS DATE
-- FROM TB_HOLIDAY_CODE
--)
OPTION (MAXRECURSION 0)
결과물
Dates DateWD
----------------------- -----------
2019-05-01 00:00:00.000 4
2019-05-02 00:00:00.000 5
2019-05-03 00:00:00.000 6
2019-05-04 00:00:00.000 7
2019-05-05 00:00:00.000 1
2019-05-06 00:00:00.000 2
2019-05-07 00:00:00.000 3
2019-05-08 00:00:00.000 4