mssql 날짜 관련 , 최근한달, 1년달력, 몇째주인지, 전달 마지막날, 이번달 마지막날, 다음달 마지막날
-- 최근 한달
select convert(char(10),dateadd(d,number,'2012-04-24'),120) as regdate
from master..spt_values with(nolock)
where type = 'P'
and convert(char(10),dateadd(d,number,'2012-04-24'),120) < DateAdd("m", 1, '2012-04-24')
-- 달력
select convert(char(10),dateadd(d,number,'2007-01-01'),120) dt
from master..spt_values
where type = 'P' and number <= datediff(d,'2007-01-01','2008-12-31')
-- 몇째주인지
DECLARE @WKDATE AS CHAR(8)
SET @WKDATE = CONVERT(CHAR(8) , getdate(), 112)
SELECT DATEPART(WK, @WKDATE) - DATEPART(WK, LEFT(@WKDATE, 6) + '01') + 1
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) LastDay_NextMonth
--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)) LastDay_AnyMonth
Following queries can be used to get the first/last days of a month.
To get first day of a month use:
– © 2011 – Vishal (http://SqlAndMe.com)
– First Day Previous/Current/Next Months
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),
'First Day of Previous Month'
UNION ALL
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) - 1),
DATEADD(MONTH, -1, GETDATE())),
'First Day of Previous Month (2)'
UNION ALL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
'First Day of Current Month'
UNION ALL
SELECT DATEADD(DAY, -(DAY(GETDATE()) - 1), GETDATE()),
'First Day of Current Month (2)'
UNION ALL
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0),
'First Day of Next Month'
UNION ALL
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) - 1),
DATEADD(MONTH, 1, GETDATE())),
'First Day of Next Month (2)'
Result Set:
———————– ——————————-
2011-04-01 00:00:00.000 First Day of Previous Month
2011-04-01 15:47:36.660 First Day of Previous Month (2)
2011-05-01 00:00:00.000 First Day of Current Month
2011-05-01 15:47:36.660 First Day of Current Month (2)
2011-06-01 00:00:00.000 First Day of Next Month
2011-06-01 15:47:36.660 First Day of Next Month (2)
(6 row(s) affected)
The above queries can be generalized as below:
DECLARE @DURATION INT = 2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)
AS '+2 Months'
SET @DURATION = -2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)
AS '-2 Months'
Result Set:
+2 Months
———————–
2011-07-01 00:00:00.000
(1 row(s) affected)
-2 Months
———————–
2011-03-01 00:00:00.000
(1 row(s) affected)
And, to get last day of a month use:
– © 2011 – Vishal (http://SqlAndMe.com)
– Last Day Previous/Current/Next Months
SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE()),
'Last Day of Previous Month'
UNION ALL
SELECT DATEADD(MILLISECOND, -3,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),
'Last Day of Previous Month (2)'
UNION ALL
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),
DATEADD(MONTH, 1, GETDATE())),
'Last Day of Current Month'
UNION ALL
SELECT DATEADD(MILLISECOND, -3,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)),
'Last Day of Current Month (2)'
UNION ALL
SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),
DATEADD(MONTH, 2, GETDATE())),
'Last Day of Next Month'
UNION ALL
SELECT DATEADD(SECOND, -1,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)),
'Last Day of Next Month (2)'
Result Set:
———————– ——————————
2011-04-30 15:54:35.523 Last Day of Previous Month
2011-04-30 23:59:59.997 Last Day of Previous Month (2)
2011-05-31 15:54:35.523 Last Day of Current Month
2011-05-31 23:59:59.997 Last Day of Current Month (2)
2011-06-30 15:54:35.523 Last Day of Next Month
2011-06-30 23:59:59.000 Last Day of Next Month (2)
(6 row(s) affected)
The above queries can be generalized as below:
DECLARE @DURATION INT = 2
SELECT DATEADD(MILLISECOND, -3,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))
AS '+2 Months'
SET @DURATION = -2
SELECT DATEADD(MILLISECOND, -3,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))
AS '-2 Months'
Result Set:
+2 Months
———————–
2011-07-31 23:59:59.997
(1 row(s) affected)
-2 Months
———————–
2011-03-31 23:59:59.997
(1 row(s) affected)
Hope This Helps!
Vishal
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value,'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
'First Day of Next Month'
GO
