在 SQL 中获取一个月的最后一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1051488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the last day of the month in SQL
提问by Byron Whitlock
I need to get the last day of the month given as a date in SQL. If I have the first day of the month, I can do something like this:
我需要获取在 SQL 中作为日期给出的月份的最后一天。如果我有一个月的第一天，我可以做这样的事情：
DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1)
But does anyone know how to generalize it so I can find the last day of the month for any given date?
但是有谁知道如何概括它以便我可以找到任何给定日期的月份的最后一天？
回答by Martin Smith
From SQL Server 2012 you can use the EOMONTHfunction.
从 SQL Server 2012 开始，您可以使用EOMONTH函数。
Returns the last day of the month that contains the specified date, with an optional offset.
返回包含指定日期的月份的最后一天，并带有可选的偏移量。
Syntax
句法
EOMONTH ( start_date [, month_to_add ] )
How ... I can find the last day of the month for any given date?
如何...我可以找到任何给定日期的月份的最后一天？
SELECT EOMONTH(@SomeGivenDate)
回答by LukeH
Here's my version. No string manipulation or casting required, just one call each to the DATEADD, YEARand MONTHfunctions:
这是我的版本。不需要字符串操作或强制转换，只需对DATEADD,YEAR和MONTH函数各调用一次：
DECLARE @test DATETIME
SET @test = GETDATE() -- or any other date
SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)
回答by jamuraa
回答by Moy
I know this is a old question but here is another solution that works for me
我知道这是一个老问题，但这是另一个对我有用的解决方案
SET @dtDate = "your date"
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
And if some one is looking for different examples here is a link http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
如果有人正在寻找不同的例子，这里是一个链接http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next /
I hope this helps some one else. stackoverflow Rocks!!!!
我希望这对其他人有所帮助。stackoverflow 摇滚！！！！
回答by Sri
Works in SQL server
在 SQL 服务器中工作
Declare @GivenDate datetime
SET @GivenDate = GETDATE()
Select DATEADD(MM,DATEDIFF(MM, 0, @GivenDate),0) --First day of the month
Select DATEADD(MM,DATEDIFF(MM, -1, @GivenDate),-1) --Last day of the month
回答by Stu
Based on the statements:
基于以下声明：
SELECT DATEADD(MONTH, 1, @x) -- Add a month to the supplied date @x
and
和
SELECT DATEADD(DAY, 0 - DAY(@x), @x) -- Get last day of month previous to the supplied date @x
how about adding a month to date @x and then retrieving the last day of the month previous to that (i.e. The last day of the month of the supplied date)
如何添加一个月到日期@x 然后检索前一个月的最后一天（即提供日期的月份的最后一天）
DECLARE @x DATE = '20-Feb-2012'
SELECT DAY(DATEADD(DAY, 0 - DAY(DATEADD(MONTH, 1, @x)), DATEADD(MONTH, 1, @x)))
Note: This was test using SQL Server 2008 R2
注意：这是使用 SQL Server 2008 R2 进行的测试
回答by Eric
Just extend your formula out a little bit:
只需稍微扩展您的公式：
dateadd(day, -1,
dateadd(month, 1,
cast(month('5/15/2009') as varchar(2)) +
'/1/' +
cast(year('5/15/2009') as varchar(4)))
回答by Satinder singh
For SQL server 2012 or above use EOMONTH to get the last date of month
对于 SQL Server 2012 或更高版本，使用EOMONTH 获取月份的最后一天
SQL query to display end date of current month
显示当月结束日期的 SQL 查询
DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate) AS CurrentMonthED
SQL query to display end date of Next month
显示下个月结束日期的 SQL 查询
DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate, 1 ) AS NextMonthED
回答by Serkan Arslan
This query can also be used.
也可以使用此查询。
DECLARE @SelectedDate DATE = GETDATE()
SELECT DATEADD(DAY, - DAY(@SelectedDate), DATEADD(MONTH, 1 , @SelectedDate)) EndOfMonth
回答by David
WinSQL to get last day of last month (i.e today is 2017-02-09, returns 2017-01-31: Select dateadd(day,-day(today()),today())
WinSQL 获取上个月的最后一天（即今天是 2017-02-09，返回 2017-01-31：选择 dateadd(day,-day(today()),today()）
