判断一个日期是否为月末

 

原文：http://www.sqlservercurry.com/2011/06/sql-server-date-end-month.html

 

SQL Server: Check if Date is End of Month

判断一个日期是否为月末

Here’s a very simple query that checks if the date is End of the Month.

这是一句简单的用于判断一个日期是否为月末的查询语句。

DECLARE @Dt DATETIME 

 
SET @Dt = '2011-06-30'

 
IF MONTH(@Dt) != MONTH(DATEADD(DAY,1,@Dt))

BEGIN

    PRINT 'End Of Month'

END

As you can see, we are using MONTH(DATEADD(DAY,1,@Dt)) which if run for the June date returns 7 if we are the end of the month. For any other June date, it will return 6. All we are doing is comparing it with MONTH(@Dt) to see if the two values match. If it does not, it is the end of the month.

显而易见，我们使用了MONTH(DATEADD(DAY,1,@Dt))，如果是6月某天且为月末，该函数返回7.对于6月的其他日期，其返回值为6.我们只需使用MONTH(@Dt)来判断两个月份是否相同。如果不同，该日期即为月末。

OUTPUT

-------------

End Of Month

 

SQLServerCurry.com author Madhivanan has suggested an alternate method to check if the given date is the last date of the month. Here it is:
SQLServerCurry.com的作者Madhivanan建议另一种方法来判断给定日期是否为月末。该语句如下，

DECLARE @Dt DATETIME 

SET @Dt = '2011-06-30'

IF DAY(@Dt+1) = 1
BEGIN
    PRINT 'End Of Month'
END
ELSE
BEGIN
    PRINT 'Not End Of Month'
END

The code DAY(@Dt+1) adds 1 day to the given date. If the given date is last day of month, the date becomes first day of next month so the DAY function will return 1. Otherwise it is not the last day of month。否则给定日期不为月末。

代码DAY(@Dt+1)将指定日期加一天。如果给定日期为月末，则加一天后的日期将成为下月的第一天，所以DAY函数会返回1.
Update: Brad Schulz made a good point on this alternate method. He says "It will not work with the SQL2008 date datatype. You can not add an integer to a date datatype like you could with datetimes. So instead of DAY(@Dt+1) it is best to do DAY(DATEADD(day,1,@Dt)), which will work in all versions." Thanks Brad!

更新信息：Brad Schulz针对后一种方法提出了很好的见解。他讲到:“该方法对于SQL2008的日期类型将不能工作。你不能对一个date类型象操作一个datetime类型一样加一个整型数。所以最好的办法是用DAY(DATEADD(day,1,@Dt))替换DAY(@Dt+1)。这样就能在所有版本中正常工作了”

谢谢Brad Schulz