使用SQL获取每个月的最后一天

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?

但是有没有人知道如何概括它，这样我就能找到每个给定日期的最后一天?

18 个解决方案

#1

Here's my version. No string manipulation or casting required, just one call each to the DATEADD, YEAR and MONTH functions:

这是我的版本。不需要进行字符串操作或强制转换，只需分别调用DATEADD、YEAR和MONTH函数:

DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)

#2

From SQL Server 2012 you can use the EOMONTH function.

从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)

#3

You could get the days in the date by using the DAY() function:

可以使用DAY()函数获取日期中的天数:

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

#4

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进行的测试

#5

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)))

#6

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- month- of- last- month- last- month- term -month- last- month- the -last- month- the -last- month- the -last- month- last- month- last- month- last- month- the -last- month- last- month- last- month- last- month-

I hope this helps some one else. * Rocks!!!!

我希望这能帮助别人。*岩石! ! ! !

#7

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

#8

Using SQL Server, here is another way to find last day of month :

使用SQL Server，这里有另一种方法可以找到每个月的最后一天:

SELECT DATEADD(MONTH,1,GETDATE())- day(DATEADD(MONTH,1,GETDATE()))

#9

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())

从上个月的最后一天开始。e今天是2017-02-09,return 2017-01-31: Select dateadd(day，-day,today()，today())

#10

Try to run the following query, it will give you everything you want :)

尝试运行以下查询，它将提供您所需的一切:)

Declare @a date =dateadd(mm, Datediff(mm,0,getdate()),0)
Print('First day of Current Month:')
Print(@a)
Print('')
set @a = dateadd(mm, Datediff(mm,0,getdate())+1,-1)
Print('Last day of Current Month:')
Print(@a)
Print('')

Print('First day of Last Month:')
set @a = dateadd(mm, Datediff(mm,0,getdate())-1,0)
Print(@a)
Print('')

Print('Last day of Last Month:') 
set @a = dateadd(mm, Datediff(mm,0,getdate()),-1)
Print(@a)
Print('')


Print('First day of Current Week:')
set @a = dateadd(ww, Datediff(ww,0,getdate()),0)
Print(@a)
Print('')

Print('Last day of Current Week:')
set @a = dateadd(ww, Datediff(ww,0,getdate())+1,-1)
Print(@a)
Print('')

Print('First day of Last Week:')
set @a =  dateadd(ww, Datediff(ww,0,getdate())-1,0)
Print(@a)
Print('')

Print('Last day of Last Week:')
set @a =  dateadd(ww, Datediff(ww,0,getdate()),-1)
Print(@a)

#11

WinSQL: I wanted to return all records for last month:

WinSQL:我想要返回上个月的所有记录:

where DATE01 between dateadd(month,-1,dateadd(day,1,dateadd(day,-day(today()),today()))) and dateadd(day,-day(today()),today())

This does the same thing:

这是一样的:

where month(DATE01) = month(dateadd(month,-1,today())) and year(DATE01) = year(dateadd(month,-1,today()))

#12

My 2 cents:

我的2美分。

select DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(day,(0-(DATEPART(dd,'2008-02-12')-1)),'2008-02-12')))

Raj

拉吉

#13

If you need it frequently, wrap it in an inline TVF, which is very fast:

如果你经常需要的话，可以用内联的TVF包装，速度非常快:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/21/calculating-third-wednesday-of-the-month-with-inline-udfs.aspx ,

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/21/calculating-third-wednesday-of-the-month-with-inline-udfs.aspx,

#14

using sql server 2005, this works for me:

使用sql server 2005，这对我来说是可行的:

select dateadd(dd,-1,dateadd(mm,datediff(mm,0,YOUR_DATE)+1,0))

Basically, you get the number of months from the beginning of (SQL Server) time for YOUR_DATE. Then add one to it to get the sequence number of the next month. Then you add this number of months to 0 to get a date that is the first day of the next month. From this you then subtract a day to get to the last day of YOUR_DATE.

基本上，您可以从(SQL Server)开始获取您的_date的月数。然后加上一个，得到下个月的序列号。然后你把这几个月的时间加到0，以得到下个月的第一天的日期。然后减去一天，直到约会的最后一天。

#15

This works for me, using Microsoft SQL Server 2005:

这适用于我，使用Microsoft SQL Server 2005:

DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'2009-05-01')+1,0))

#16

Take some base date which is the 31st of some month e.g. '20011231'. Then use the
following procedure (I have given 3 identical examples below, only the @dt value differs).

以某个月31日为基准日。“20011231”。然后使用以下过程(我在下面给出了3个相同的示例，只有@dt值不同)。

declare @dt datetime;

set @dt = '20140312'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140208'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140405'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');

#17

I wrote following function, it works.

我写了如下函数，它是有效的。

It returns datetime data type. Zero hour, minute, second, miliseconds.

它返回datetime数据类型。零小时，一分钟，一秒，一毫秒。

CREATE Function [dbo].[fn_GetLastDate]
(
    @date datetime
)
returns datetime
as
begin

declare @result datetime

 select @result = CHOOSE(month(@date),  
 DATEADD(DAY, 31 -day(@date), @date),
 IIF(YEAR(@date) % 4 = 0, DATEADD(DAY, 29 -day(@date), @date), DATEADD(DAY, 28 -day(@date), @date)), 
 DATEADD(DAY, 31 -day(@date), @date) ,
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date))

 return convert(date, @result)

end

It's very easy to use. 2 example:

它很容易使用。2的例子:

select [dbo].[fn_GetLastDate]('2016-02-03 12:34:12')

select [dbo].[fn_GetLastDate](GETDATE())

#18

The LAST_DAY function works nicely for this.

LAST_DAY函数很好用。

Edit: Actually as mentioned in the comments this probably not for all SQL. I'll leave the post in the possibility of someone looking for a MySQL/Oracle answer stumbling upon it.

编辑:实际上，正如评论中提到的，这可能并不适用于所有SQL。我把这篇文章留给那些寻找MySQL/Oracle答案的人。

From the mysql reference manual:

从mysql参考手册:

LAST_DAY(date)

Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

获取日期或日期时间值，并返回该月最后一天的相应值。如果参数无效，返回NULL。

mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

LAST_DAY() is available as of MySQL 4.1.1.

LAST_DAY()可从MySQL 4.1.1获得。