Oracle NEXT_DAY 함수를 MSSQL NEXT_DAY 로 만들기

Oracle의 NEXT_DAY 함수는 입력한 날짜 이후의 찾고자 하는 요일의 첫 번째 일자를 반한 합니다.

NEXT_DAY(날짜, 찾고자 하는 요일)

- 찾고자 하는 요일 : 1=일, 2=월, 3=화, 4=수, 5=목, 6=금, 7=토 

MSSQL 에는 대응되는 함수가 없기때문에 사용자 함수를 만들어 사용하셔야 합니다.

CREATE FUNCTION [dbo].[NEXT_DAY] (@p_date DATETIME, @p_WEEKNO INT) 
RETURNS DATETIME 
AS 
BEGIN 
DECLARE @next_day DATETIME; 

SELECT @next_day = DATEADD(DAY,n, @p_date) 
FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) AS t(n) 
WHERE DATEPART(WEEKDAY, DATEADD(DAY,n, @p_date)) = @p_WEEKNO 
OR CONVERT(varchar(10), DATEPART(WEEKDAY, DATEADD(DAY,n, @p_date))) = @p_WEEKNO 

RETURN @next_day 
END 

GO

Oracle 의 NEXT_DAY 와 비교한 결과는

지정일	검색 요일	Oracle	MSSQL
2020-02-16 23:56:17.077	1	2020-02-23 23:05:26	2020-02-23 23:56:17.077
2020-02-16 23:56:17.077	2	2020-02-17 23:05:26	2020-02-17 23:56:17.077
2020-02-16 23:56:17.077	3	2020-02-18 23:05:26	2020-02-18 23:56:17.077
2020-02-16 23:56:17.077	4	2020-02-19 23:05:26	2020-02-19 23:56:17.077
2020-02-16 23:56:17.077	5	2020-02-20 23:05:26	2020-02-20 23:56:17.077
2020-02-16 23:56:17.077	6	2020-02-21 23:05:26	2020-02-21 23:56:17.077
2020-02-16 23:56:17.077	7	2020-02-22 23:05:26	2020-02-22 23:56:17.077
2020-02-16 23:56:17.077	1	2020-03-01 23:05:26	2020-03-01 23:56:17.077
2020-02-16 23:56:17.077	2	2020-03-16 23:05:26	2020-03-16 23:56:17.077
2020-03-20 23:56:17.077	3	2020-03-24 23:05:26	2020-03-24 23:56:17.077
2020-03-31 23:56:17.077	4	2020-04-01 23:05:26	2020-04-01 23:56:17.077
2020-04-11 23:56:17.077	5	2020-04-16 23:05:26	2020-04-16 23:56:17.077
2020-04-22 23:56:17.077	6	2020-04-24 23:05:26	2020-04-24 23:56:17.077
2020-05-03 23:56:17.077	7	2020-05-09 23:05:26	2020-05-09 23:56:17.077

 

궁금증은 덧글로 편하게 남겨주세요.