如何在 SQL 中计算一个月的最后一天？

How do I calculate the last day of the month in SQL?

提问by David Collie

Specifically MSSQL 2005.

特别是 MSSQL 2005。

回答by Bill the Lizard

Here's a solution that gives you the last second of the current month. You can extract the date part or modify it to return just the day. I tested this on SQL Server 2005.

这是一个解决方案，可为您提供当月的最后一秒。您可以提取日期部分或修改它以仅返回日期。我在 SQL Server 2005 上对此进行了测试。

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );

To understand how it works we have to look at the dateadd()and datediff()functions.

要了解它是如何工作的，我们必须查看dateadd()和datediff()函数。

DATEADD(datepart, number, date)  
DATEDIFF(datepart, startdate, enddate)

If you run just the most inner call to datediff(), you get the current month number since timestamp 0.

如果您只运行最内部的 datediff() 调用，您将获得自时间戳 0 以来的当前月份数。

select datediff(m, 0, getdate() );  
1327

The next part adds that number of months plus 1 to the 0 timestamp, giving you the starting point of the next calendar month.

下一部分将月数加 1 添加到 0 时间戳，为您提供下一个日历月的起点。

select dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 );
2010-09-01 00:00:00.000

Finally, the outer dateadd() just subtracts one second from the beginning timestamp of next month, giving you the last second of the current month.

最后，外部 dateadd() 只是从下个月的开始时间戳减去一秒，为您提供当月的最后一秒。

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );
2010-08-31 23:59:59.000

---

---

This old answer (below) has a bug where it doesn't work on the last day of a month that has more days than the next month. I'm leaving it here as a warning to others.这个旧答案（如下）有一个错误，它在一个月的最后一天不起作用，比下个月的天数多。我把它留在这里作为对其他人的警告。

Add one month to the current date, and then subtract the value returned by the DAY function applied to the current date using the functions DAY and DATEADD.

将当前日期加一个月，然后减去使用函数 DAY 和 DATEADD 应用于当前日期的 DAY 函数返回的值。

dateadd(day, -day(getdate()), dateadd(month, 1, getdate()))

回答by onedaywhen

SELECT DATEADD(M, DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP), '1990-01-31T00:00:00.000')

Explanation:

解释：

General approach: use temporal functionality.

一般方法：使用时间功能。

SELECT '1990-01-01T00:00:00.000', '1990-01-31T00:00:00.000'

These are DATETIME literals, being the first time granule on the first day and last day respectively of the same 31-day month. Which month is chosen is entirely arbitrary.

这些是 DATETIME 文字，分别是同一 31 天月份的第一天和最后一天的第一个时间颗粒。选择哪个月份完全是任意的。

SELECT DATEDIFF(M, '1990-01-01T00:00:00.000', CURRENT_TIMESTAMP)

This is the difference in whole months between the first day of the reference month and the current timestamp. Let's call this @calc.

这是参考月份的第一天与当前时间戳之间整月的差异。让我们称之为@calc。

SELECT DATEADD(M, @calc, '1990-01-31T00:00:00.000')

This adds @calcmonth granules to the last day of the reference month, the result of which is the current timestamp 'rounded' to the last day of its month. Q.E. D.

这会将@calc月份颗粒添加到参考月份的最后一天，其结果是当前时间戳“四舍五入”到其月份的最后一天。QED

回答by sebagomez

Try this:

尝试这个：

DATEADD (DAY, -1, DATEADD (MONTH, DATEDIFF (MONTH, 0, CURRENT_TIMESTAMP) + 1, 0)

回答by Taherul

They key points are if you can get first day of current month,Last Day of Last Month and Last Day of Current Month.

它们的关键点是您是否可以获得当月的第一天、上个月的最后一天和当月的最后一天。

Below is the Step by Step way to write query:

以下是编写查询的分步方法：

In SQL Server Date Starts from 1901/01/01( Date 0) and up to now each month can be identified by a number. Month 12 is first month of 1902 means January. Month 1200 is January of 2001. Similarly each day can be assigned by unique number e.g Date 0 is 1901/01/01. Date 31 is 1901/02/01 as January of 1901 starts from 0.

在 SQL Server 中，日期从 1901/01/01（日期 0）开始，到现在每个月都可以用数字标识。12 月是 1902 年的第一个月，意思是一月。1200 月是 2001 年 1 月。类似地，每一天都可以通过唯一编号进行分配，例如日期 0 是 1901/01/01。日期 31 是 1901/02/01，因为 1901 年 1 月从 0 开始。

To find out First day of Current Month(Current Date or a given date)

找出当月的第一天（当前日期或给定日期）

1. First we need to check how many months have passed since date 0(1901/01/01). SELECT DATEDIFF(MM,0,GETDATE())
2. Add same number of month to date 0(1901/01/01)

   SELECT DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0)

1. 首先，我们需要检查自日期 0（1901/01/01）以来已经过去了多少个月。SELECT DATEDIFF(MM,0,GETDATE())
2. 将相同的月份数添加到日期 0(1901/01/01)

   选择 DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0)

Then we will get first day of current month(Current Date or a given date)

然后我们将获得当月的第一天（当前日期或给定日期）

To get Last Day of Last Month

获取上个月的最后一天

We need to subtract a second from first day of current month

我们需要从当月的第一天减去一秒

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0))

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE()),0))

To get Last Day of Current Month

获取当月的最后一天

To get first day of current month first we checked how many months have been passed since date 0(1901/01/01). If we add another month with the total months since date 0 and then add total months with date 0, we will get first day of next month.

为了首先获得当月的第一天，我们检查了自日期 0（1901/01/01）以来已经过去了多少个月。如果我们将自日期 0 以来的总月数再添加一个月，然后将总月数与日期 0 相加，我们将得到下个月的第一天。

SELECT DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1,0)

If we get first day of next month then to get last day of current month, all we need to subtract a second.

如果我们得到下个月的第一天然后得到当月的最后一天，我们只需要减去一秒。

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1,0))

SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1,0))

Hope that would help.

希望这会有所帮助。

回答by Ragul

Using SQL2005, you do not have access to a helpful function EOMONTH(), So you must calculate this yourself.

使用 SQL2005，您无法访问有用的函数EOMONTH()，因此您必须自己计算。

This simple function will works similar to EOMONTH

这个简单的函数将类似于EOMONTH

CREATE FUNCTION dbo.endofmonth(@date DATETIME= NULL)
RETURNS DATETIME
BEGIN
RETURN DATEADD(DD, -1, DATEADD(MM, +1, DATEADD(DD, 1 - DATEPART(DD, ISNULL(@date,GETDATE())), ISNULL(@date,GETDATE()))))
END

Query to perform:

要执行的查询：

SELECT dbo.endofmonth(DEFAULT)  --Current month-end date
SELECT dbo.endofmonth('02/25/2012') --User-defined month-end date

回答by David Aldridge

For completeness, in Oracle you'd do something like ...

为了完整起见，在 Oracle 中你会做一些类似的事情......

select add_months(trunc(sysdate,'MM'),1) ...

or

或者

select last_day(sysdate)+1 ...

回答by van

DATEADD(dd, -1, DATEADD(mm, +1, DATEADD(dd, 1 - DATEPART(dd, @myDate), @myDate)))

回答by David Collie

Some links to possible answers:

一些可能的答案的链接：

• http://www.extremeexperts.com/sql/Tips/DateTrick.aspx

• http://www.devx.com/tips/Tip/14405

• http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

• http://www.sqlservercurry.com/2008/03/find-last-day-of-month-in-sql-server.html

• http://www.extremeexperts.com/sql/Tips/DateTrick.aspx

• http://www.devx.com/tips/Tip/14405

• http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

• http://www.sqlservercurry.com/2008/03/find-last-day-of-month-in-sql-server.html

回答by Amy B

DECLARE
  @Now datetime,
  @Today datetime,
  @ThisMonth datetime,
  @NextMonth datetime,
  @LastDayThisMonth datetime

SET @Now = getdate()
SET @Today = DateAdd(dd, DateDiff(dd, 0, @Now), 0)
SET @ThisMonth = DateAdd(mm, DateDiff(mm, 0, @Now), 0)
SET @NextMonth = DateAdd(mm, 1, @ThisMonth)
SET @LastDayThisMonth = DateAdd(dd, -1, @NextMonth)

Sometimes you really do need the last day of this month, but frequently what you really want is to describe the time interval of this month. This is the best way to describe the time interval of this month:

有时您确实需要本月的最后一天，但通常您真正想要的是描述本月的时间间隔。这是描述本月时间间隔的最佳方式：

WHERE @ThisMonth <= someDate and someDate < @NextMonth