查询一个月的最后一天

I need to find the last day of a month in the following format:

我需要以以下格式找到一个月的最后一天:

"2013-05-31 00:00:00:000"

Anybody please help out.

有人请帮忙。

9 个解决方案

#1

Try this one -

试试这个,

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

查询:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

输出:

-----------------------
2013-05-31 00:00:00.000

#2

I know this question was for SQL Server 2005, but I thought I'd mention- as of SQL 2012, there now is an EOMONTH() function that gets the last day of the month. To get it in the format specified by the original asker you'd have to cast to a datetime.

我知道这个问题是针对SQL Server 2005的，但是我想我应该提到的是——对于SQL 2012，现在有一个EOMONTH()函数，它会在每个月的最后一天出现。要获得原始请求者指定的格式，您必须强制转换到datetime。

SELECT CAST(eomonth(GETDATE()) AS datetime)

#3

SQL Server 2012 introduces the eomonth function:

SQL Server 2012引入了eomonth功能:

select eomonth('2013-05-31 00:00:00:000')
-->
2013-05-31

#4

declare @date datetime;
set @date = getdate(); -- or some date
select dateadd(month,1+datediff(month,0,@date),-1);

#5

dateadd(month,1+datediff(month,0,getdate()),-1)

To check run:

检查运行:

print dateadd(month,1+datediff(month,0,@date),-1)

#6

Calculate the last date of the month is quite simple calculation -

计算一个月的最后一天是相当简单的计算-

Find the total months count till today's date using DATEDIFF(..,..,..) function - Select DATEDIFF(MM,0,GETDATE())
使用DATEDIFF(.. .. ..)函数查找到今天日期的月数——选择DATEDIFF(MM,0,GETDATE())

Output - 1374, If getdate() output is "2014-07-23 19:33:46.850"

输出- 1374，如果getdate()输出为“2014-07-23 19:33:46.850”

Increment by 1 into total months count - Select DATEDIFF(MM,0,GETDATE())+1
增加1到月总数-选择DATEDIFF(MM,0,GETDATE())+1

Output - 1375, If getdate() output is "2014-07-23 19:33:46.850"

输出- 1375，如果getdate()输出为“2014-07-23 19:33:48 .850”

Get the first date of next month - Select DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)
获取下个月的第一个日期—选择DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

Output - '2014-08-01 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

输出- '2014-08-01 00:00.000'，如果getdate()输出是"2014-07-23 19:33:33:49 .850"

Subtract by -1 into the first date of next month, which will return last date of the current month - Select DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0))
下个月的第一个日期减去-1，它将返回当月最后一个日期——选择DATEADD(DD，-1,DATEADD(MM,DATEDIFF, MM,0,GETDATE() +1,0))

Output - '2014-07-31 00:00:00.000', If getdate() output is "2014-07-23 19:33:46.850"

输出- '2014-07-31 00:01 .000'，如果getdate()输出是"2014-07-23 19:33:49 .850"

In the same manner of calculation we can achieve the - 1. Last date of next month 2. Last date of the previous month and so on...

用同样的计算方法，我们可以得到- 1。下个月的最后一个日期。上个月的最后一天等等……

I hope this article will help.

我希望这篇文章能有所帮助。

#7

Select DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))

选择DATEADD(DAY， - DAY(DATEADD(MONTH, 1, GETDATE())))，DATEADD(MONTH, 1, GETDATE())))

This works great in T-sql ..

这在T-sql中非常有用。

Replace the GETDATE() of the query with your column name .

用列名替换查询的GETDATE()。

#8

TO FIND 1ST and Last day of the Previous, Current and Next Month in Oracle SQL
-----------------------------------------------------------------------------
SELECT 
SYSDATE,
LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 FDPM,
LAST_DAY(ADD_MONTHS(SYSDATE,-1)) LDPM,
LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1 FDCM,
LAST_DAY(SYSDATE)LDCM,
LAST_DAY(SYSDATE)+1 FDNM,
LAST_DAY(LAST_DAY(SYSDATE)+1) LDNM
FROM DUAL

#9

-1

http://php.net/manual/en/function.cal-days-in-month.php

The length in days of the selected month in the given calendar

给定日历中选定月份的天数

<?php
$num = cal_days_in_month(CAL_GREGORIAN, 8, 2003); // 31
echo "There was $num days in August 2003";
?>

#1