如何获取 SQL Server 2008 的最后一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26651365/
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
How to get last date of month SQL Server 2008
提问by Phattharawit Sombatrat
I created a function "ufngetFirstDateOfMonth" and "ufngetLastDateOfMonth" stored in Microsoft SQL Server 2008. My purpose is to send some date into the function and it will return the first date of month with '00:00:00' or the last date of month with '23:59:59'.
我创建了一个存储在 Microsoft SQL Server 2008 中的函数“ufngetFirstDateOfMonth”和“ufngetLastDateOfMonth”。我的目的是将一些日期发送到函数中，它将返回带有“00:00:00”或最后日期的月份的第一个日期'23:59:59' 的月份。
I call the function like this:
我这样调用函数：
exec ufngetLastDateOfMonth('2014-10-15')
and normally it returns '2014-10-31 23:59:59'
but when I send the last date of months that have 31 days (august, january,...):
通常它会返回 '2014-10-31 23:59:59'
但是当我发送有 31 天（八月、一月...）的月份的最后日期时：
exec ufngetLastDateOfMonth('2014-10-31')
it return '2014-10-30 23:59:59' whick is not correct Actally, it should be '2014-10-31 23:59:59'
它返回 '2014-10-30 23:59:59' 这不正确实际上，它应该是 '2014-10-31 23:59:59'
Something goes wrong here...
这里出了点问题...
This is my function:
这是我的功能：
CREATE FUNCTION [dbo].[ufnLastDateOfMonth](@Date date)
RETURNS varchar(50)
AS
BEGIN
DECLARE @New_Date varchar(50)
select @New_date = cast(dateadd(dd,-(DAY(@Date )),DATEADD(mm,1,@Date ))as varchar(50)) + ' 23:59:59'
RETURN @New_Date
END
回答by Tanner
To get the last day you can do this:
要获得最后一天，您可以这样做：
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2014-08-12')+1,0))
Adding to your function:
添加到您的功能：
select @New_date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))
Source:
来源：
SQL SERVER – Find Last Day of Any Month – Current Previous Next
回答by Syakur Rahman
For those who are using SQL Server 2012,
EOMONTHfunction could be an alternative.
对于那些使用 SQL Server 2012 的人来说，
EOMONTH函数可能是另一种选择。
DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result;
GO
Source: https://msdn.microsoft.com/en-us/library/hh213020.aspx
回答by Gordon Linoff
Go to the first day of the month. Add one month. Then subtract one day. Or, in your case, one second:
转到该月的第一天。加一个月。然后减去一天。或者，就您而言，一秒钟：
select @New_date = dateadd(second, -1, dateadd(month, 1, dateadd(day, -(DAY(@Date) + 1)) ) )
You should use convert()if you want this as a string. I would instead suggest that the function return a datetime.
convert()如果你想把它作为一个字符串，你应该使用。相反，我建议该函数返回一个datetime.
回答by Manish Sharma
Use this
用这个
DECLARE @curdate datetime;
SET @curdate = GETDATE(); -- you can pass your date here
--SET @curdate = '2014-10-15'; Example
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,@curdate),-1);
OR
或者
SELECT DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),-1);
回答by Khwahish Batra
you can try this
你可以试试这个
select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)) As EndDateOfCurrentMonth
or this
或这个
select DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) As StartDateOfCurrentMonth
