SQL Server query for first and last day of month

dl5txlt9 于 12个月前发布在其他

关注(0)|答案(3)|浏览(105)

If I have the name of the month, how can I have the first and last day of that month in SQL?

I have this query to returns the month names:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)

Result:

Now, how can i get the first and last day of that months? changing the query.

sql-server

来源：https://stackoverflow.com/questions/15742605/query-for-first-and-last-day-of-month

3条答案

按热度按时间

wn9m85ua1#

If you want a more general and simple solution:

DECLARE @startdate AS DATETIME = GETDATE()
SELECT      
DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0)   as startMonth,
DATEADD(SECOND, -1, DATEADD(MONTH, 1,  DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0) ) ) as endMonthExactly,
EOMONTH(@startdate) as endMonthDay

Gives the result:

startMonth       2023-12-01 00:00:00
endMonthExactly  2023-12-31 23:59:59
endMonthDay      2023-12-31

赞(0）回复(0）举报 12个月前

yftpprvb2#

Try this :-

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';

SELECT  DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName,
CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(DATEADD(MONTH, x.number, @StartDate))-1),DATEADD(MONTH, x.number, @StartDate)),101) as FirstDay,
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate)))),DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate))),101) as LastDay
FROM    master..spt_values x
WHERE   x.type = 'P'        
AND     x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)

Result :-

MonthName       FirstDay         LastDay   
  May           05/01/2011        05/31/2011    
 June           06/01/2011        06/30/2011    
 July           07/01/2011        07/31/2011    
 August         08/01/2011        08/31/2011

Result obtained taking the help from this query

赞(0）回复(0）举报 12个月前

jdg4fx2g3#

This code gives you the first date of Current Month:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

If you know the Month's number then:

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @MonthNumber, 0)

Same goes for Month's end date :

SELECT   DATEADD(MILLISECOND, -1,
         DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @@MonthNumber+ 1, 0))

赞(0）回复(0）举报 12个月前

我来回答

SQL Server query for first and last day of month

3条答案

相关问题

热门标签

最新问答