SQL 올 해 시작 날짜, 마지막 날짜 구하기, etc...

1. 올 해 시작일

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS 'FirstDay'                                               -- 'yyyy-dd-mm hh:mm:ss' 포맷

-- VARCHAR로 변경하기
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 23) AS 'FirstDay'                     -- 'yyyy-mm-dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 111) AS 'FirstDay'                    -- 'yyyy/mm/dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 102) AS 'FirstDay'                    -- 'yyyy.mm.dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 112) AS 'FirstDay'                    -- 'yyyymmdd' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 105) AS 'FirstDay'                    -- 'dd-mm-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 103) AS 'FirstDay'                    -- 'dd/mm/yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 104) AS 'FirstDay'                    -- 'dd.mm.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 105), '-', '') AS 'FirstDay'  -- 'ddmmyyyy' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110) AS 'FirstDay'                    -- 'mm-dd-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 101) AS 'FirstDay'                    -- 'mm/dd/yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110), '-', '.') AS 'FirstDay' -- 'mm.dd.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0), 110), '-', '') AS 'FirstDay'  -- 'mmddyyyy' 포맷

 

2. 올 해 마지막일

SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS 'LastDay'                                               -- 'yyyy-dd-mm hh:mm:ss' 포맷

-- VARCHAR로 변경하기
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 23) AS 'LastDay'                     -- 'yyyy-mm-dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 111) AS 'LastDay'                    -- 'yyyy/mm/dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 102) AS 'LastDay'                    -- 'yyyy.mm.dd' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 112) AS 'LastDay'                    -- 'yyyymmdd' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 105) AS 'LastDay'                    -- 'dd-mm-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 103) AS 'LastDay'                    -- 'dd/mm/yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 104) AS 'LastDay'                    -- 'dd.mm.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 105), '-', '') AS 'LastDay'  -- 'ddmmyyyy' 포맷

SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110) AS 'LastDay'                    -- 'mm-dd-yyyy' 포맷
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 101) AS 'LastDay'                    -- 'mm/dd/yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110), '-', '.') AS 'LastDay' -- 'mm.dd.yyyy' 포맷
SELECT REPLACE(CONVERT(VARCHAR(10), DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1), 110), '-', '') AS 'LastDay'  -- 'mmddyyyy' 포맷

 

참고

작년, 올해, 내년의 시작일과 마지막일

SELECT DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AS 'LastYearFirstDay';
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1) AS 'LastYearLastDay';

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS 'FirstDay';
SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1) AS 'LastDay';

SELECT DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), 0) AS 'NextYearFirstDay';
SELECT DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1)) AS 'to do NextYearFirstDay';

 

이번달 시작일과 마지막일

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1);

SELECT CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));
SELECT CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1));

DECLARE @FirstDOM DATETIME, @LastDOM DATETIME
SET @FirstDOM = (SELECT CONVERT(DATE, DATEADD(dd, -(DAY(GETDATE())-1), GETDATE())))
SET @LastDOM = (SELECT DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+1,0))) 
SELECT @FirstDOM,@LastDOM;

 

1년 전 시작일, 2년 전 시작일

SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));
SELECT DATEADD(YEAR, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0));

 

오늘 00:00:00, 23:59:59

SELECT DATEADD(d,0,DATEDIFF(d,0,GETDATE()));
SELECT DATEADD(s,-1,DATEDIFF(d,-1,GETDATE()));

 

 

Tag. sql this month first day, sql this month last day. sql this year first day, sql this year last day, sql next year first day, sql next year last day, sql last year first day, sql last year last day, sql last year, sql recent year, sql last 1 year. sql recent 1 year, sql last 2 years, sql recent 2 years