如何确定 SQL Server 中一个月的天数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/691022/
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 determine the number of days in a month in SQL Server?
提问by Even Mien
I need to determine the number of days in a month for a given date in SQL Server.
我需要确定 SQL Server 中给定日期的一个月中的天数。
Is there a built-in function? If not, what should I use as the user-defined function?
有内置函数吗？如果没有，我应该使用什么作为用户定义的函数？
回答by Mikael Eriksson
In SQL Server 2012 you can use EOMONTH (Transact-SQL)to get the last day of the month and then you can use DAY (Transact-SQL)to get the number of days in the month.
在 SQL Server 2012 中，您可以使用EOMONTH (Transact-SQL)获取该月的最后一天，然后您可以使用DAY (Transact-SQL)获取该月的天数。
DECLARE @ADate DATETIME
SET @ADate = GETDATE()
SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth
回答by Mehrdad Afshari
You can use the following with the first day of the specified month:
您可以在指定月份的第一天使用以下内容：
datediff(day, @date, dateadd(month, 1, @date))
To make it work for every date:
要使其适用于每个日期：
datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
回答by Daniel Davis
Most elegant solution: works for any @DATE
最优雅的解决方案：适用于任何@DATE
DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DATE),0)))
Throw it in a function or just use it inline. This answers the original question without all the extra junk in the other answers.
把它扔到一个函数中或者只是内联使用它。这回答了原始问题，而没有其他答案中的所有额外垃圾。
examples for dates from other answers:
其他答案中的日期示例：
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'1/31/2009'),0)))Returns 31
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'1/31/2009'),0)))返回 31
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0)))Returns 29
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0)))返回 29
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0)))Returns 31
SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0)))返回 31
回答by Javier
Much simpler...try day(eomonth(@Date))
更简单...试试 day(eomonth(@Date))
回答by Brimstedt
--Last Day of Previous Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))
--Last Day of Current Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
--Last Day of Next Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))
Personally though, I would make a UDF for it if there is not a built in function...
不过就我个人而言，如果没有内置函数，我会为它制作一个 UDF...
回答by gvschijndel
I would suggest:
我会建议：
SELECT DAY(EOMONTH(GETDATE()))
回答by Prashant
This code gets you the number of days in current month:
此代码为您提供当月的天数：
SELECT datediff(dd,getdate(),dateadd(mm,1,getdate())) as datas
Change getdate()to the date you need to count days for.
更改getdate()为您需要计算天数的日期。
回答by Saikh Rakif
--- sql server below 2012---
select day( dateadd(day,-1,dateadd(month, 1, convert(date,'2019-03-01'))))
-- this for sql server 2012--
select day(EOMONTH(getdate()))
回答by kritika
select add_months(trunc(sysdate,'MM'),1) - trunc(sysdate,'MM') from dual;
回答by kritika
You do need to add a function, but it's a simple one. I use this:
您确实需要添加一个函数，但它很简单。我用这个：
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
SET @pDate = @pDate - DAY(@pDate) + 1
RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))
END
GO
