如何在SQL Server中确定一个月的天数?

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?

有内置函数吗?如果不是，我应该使用什么作为用户定义的函数?

24 个解决方案

#1

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

#2

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

#3

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

选择一天(返回(返回DD,1(MM,DATEDIFF(毫米,1,' 1/31/2009 '),0)))返回31

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0))) Returns 29

选择一天(返回(返回DD,1(MM,DATEDIFF(毫米,1、2404 - 2月15日),0)))返回29

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0))) Returns 31

选择一天(返回(返回DD,1(MM,DATEDIFF(毫米,1,' 2011-12-22 '),0)))返回31

#4

Much simpler...try day(eomonth(@Date))

简单多了…试着一天(eomonth(@Date))

#5

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

#6

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()更改为需要计算天数的日期。

#7

I would suggest:

我建议:

select day(EOMONTH(getdate()))

选择一天(EOMONTH(获取当前日期()))

#8

Solution 1: Find the number of days in whatever month we're currently in

解决方案1:找出我们当前所在月份的天数

DECLARE @dt datetime
SET     @dt = getdate()

SELECT @dt AS [DateTime],
       DAY(DATEADD(mm, DATEDIFF(mm, -1, @dt), -1)) AS [Days in Month]

Solution 2: Find the number of days in a given month-year combo

解决方案2:找出给定一个月的组合中的天数。

DECLARE @y int, @m int
SET     @y = 2012
SET     @m = 2

SELECT @y AS [Year],
       @m AS [Month],
       DATEDIFF(DAY,
                DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)),
                DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0))
               ) AS [Days in Month]

#9

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

#10

SELECT Datediff(day,
(Convert(DateTime,Convert(varchar(2),Month(getdate()))+'/01/'+Convert(varchar(4),Year(getdate())))),
(Convert(DateTime,Convert(varchar(2),Month(getdate())+1)+'/01/'+Convert(varchar(4),Year(getdate()))))) as [No.of Days in a Month]

#11

select  datediff(day, 
        dateadd(day, 0, dateadd(month, ((2013 - 1900) * 12) + 3 - 1, 0)),
        dateadd(day, 0, dateadd(month, ((2013  - 1900) * 12) + 3, 0))
        )

Nice Simple and does not require creating any functions Work Fine

很简单，不需要创建任何函数

#12

You need to create a function, but it is for your own convenience. It works perfect and I never encountered any faulty computations using this function.

您需要创建一个函数，但这是为了您自己的方便。它工作的很好，我从来没有遇到任何错误的计算使用这个函数。

CREATE FUNCTION [dbo].[get_days](@date datetime)
RETURNS int
AS
BEGIN
    SET @date = DATEADD(MONTH, 1, @date)
    DECLARE @result int = (select DAY(DATEADD(DAY, -DAY(@date), @date)))
    RETURN @result
END

How it works: subtracting the date's day number from the date itself gives you the last day of previous month. So, you need to add one month to the given date, subtract the day number and get the day component of the result.

它的工作原理:从日期本身减去日期日号，就会得到上个月的最后一天。所以，你需要在给定的日期上增加一个月，减去日数，得到结果的日分量。

#13

I upvoted Mehrdad, but this works as well. :)

我对Mehrdad投了赞成票，但这也同样有效。:)

CREATE function dbo.IsLeapYear
(
    @TestYear int
)
RETURNS bit
AS
BEGIN
    declare @Result bit
    set @Result = 
    cast(
        case when ((@TestYear % 4 = 0) and (@testYear % 100 != 0)) or (@TestYear % 400 = 0)
        then 1
        else 0
        end
    as bit )
    return @Result
END
GO

CREATE FUNCTION dbo.GetDaysInMonth
(
    @TestDT datetime
)
RETURNS INT
AS
BEGIN

    DECLARE @Result int 
    DECLARE @MonthNo int

    Set @MonthNo = datepart(m,@TestDT)

    Set @Result = 
    case @MonthNo
        when  1 then 31
        when  2 then 
            case 
                when dbo.IsLeapYear(datepart(yyyy,@TestDT)) = 0
                then 28
                else 29
            end
        when  3 then 31
        when  4 then 30
        when  5 then 31
        when  6 then 30
        when  7 then 31
        when  8 then 31
        when  9 then 30 
        when 10 then 31
        when 11 then 30 
        when 12 then 31
    end

    RETURN @Result
END
GO

To Test

测试

declare @testDT datetime;

set @testDT = '2404-feb-15';

select dbo.GetDaysInMonth(@testDT)

#14

here's another one...

这是另一个……

Select Day(DateAdd(day, -Day(DateAdd(month, 1, getdate())), 
                         DateAdd(month, 1, getdate())))

#15

I know this question is old but I thought I would share what I'm using.

我知道这个问题已经过时了，但我想分享一下我在用什么。

DECLARE @date date = '2011-12-22'

/* FindFirstDayOfMonth - Find the first date of any month */
-- Replace the day part with -01
DECLARE @firstDayOfMonth date = CAST( CAST(YEAR(@date) AS varchar(4)) + '-' + 
                                      CAST(MONTH(@date) AS varchar(2)) + '-01' AS date)
SELECT @firstDayOfMonth

and

和

DECLARE @date date = '2011-12-22'

/* FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD */
-- Get the first day of next month and remove a day from it using DATEADD
DECLARE @lastDayOfMonth date = CAST( DATEADD(dd, -1, DATEADD(mm, 1, FindFirstDayOfMonth(@date))) AS date)

SELECT @lastDayOfMonth

Those could be combine to create a single function to retrieve the number of days in a month if needed.

如果需要，可以将它们结合起来创建一个函数来检索一个月的天数。

#16

SELECT DAY(SUBDATE(ADDDATE(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-1'), INTERVAL 1 MONTH), INTERVAL 1 DAY))

Nice 'n' Simple and does not require creating any functions

很简单，不需要创建任何函数

#17

Mehrdad Afshari reply is most accurate one, apart from usual this answer is based on formal mathematical approach given by Curtis McEnroe in his blog https://cmcenroe.me/2014/12/05/days-in-month-formula.html

Mehrdad Afshari的回复是最准确的，除了通常的答案，这个答案是基于Curtis McEnroe在他的博客https://cmcenroe.me4/12/05/month -formula.html中给出的正式的数学方法

DECLARE @date  DATE= '2015-02-01'
DECLARE @monthNumber TINYINT 
DECLARE @dayCount TINYINT
SET @monthNumber = DATEPART(MONTH,@date )
SET @dayCount = 28 + (@monthNumber + floor(@monthNumber/8)) % 2 + 2 %    @monthNumber + 2 * floor(1/@monthNumber)   
SELECT @dayCount + CASE WHEN @dayCount = 28 AND DATEPART(YEAR,@date)%4 =0 THEN 1 ELSE 0 END -- leap year adjustment

#18

To get the no. of days in a month we can directly use Day() available in SQL.

没有。在一个月内，我们可以在SQL中直接使用Day()。

Follow the link posted at the end of my answer for SQL Server 2005 / 2008.

按照我的SQL Server 2005 / 2008的答案结尾的链接。

The following example and the result are from SQL 2012

下面的示例和结果来自SQL 2012

alter function dbo.[daysinm]
(
@dates nvarchar(12)
)
returns int
as
begin
Declare @dates2 nvarchar(12)
Declare @days int
begin
select @dates2 = (select DAY(EOMONTH(convert(datetime,@dates,103))))
set @days = convert(int,@dates2)
end
return @days
end

--select dbo.daysinm('08/12/2016')

Result in SQL Server SSMS

结果是SQL Server SSMS

  (no column name)
1 31

Process:

过程:

When EOMONTH is used, whichever the date format we use it is converted into DateTime format of SQL-server. Then the date output of EOMONTH() will be 2016-12-31 having 2016 as Year, 12 as Month and 31 as Days. This output when passed into Day() it gives you the total days count in the month.

使用EOMONTH时，将使用的日期格式转换为SQL-server的DateTime格式。EOMONTH()的生产日期为2016-12-31,2016年为年，12月为月，31天。这个输出传入Day()时，会给出一个月的天数计数。

If we want to get the instant result for checking we can directly run the below code,

如果我们想要得到即时结果来检查我们可以直接运行下面的代码，

select DAY(EOMONTH(convert(datetime,'08/12/2016',103)))

or

或

select DAY(EOMONTH(convert(datetime,getdate(),103)))

for reference to work in SQL Server 2005/2008/2012, please follow the following external link ...

关于SQL Server 2005/2008/2012的工作，请遵循以下外部链接…

Find No. of Days in a Month in SQL

找不到。在SQL中一个月的天数

#19

select first_day=dateadd(dd,-1*datepart(dd,getdate())+1,getdate()), last_day=dateadd(dd,-1*datepart(dd,dateadd(mm,1,getdate())),dateadd(mm,1,getdate())), no_of_days = 1+datediff(dd,dateadd(dd,-1*datepart(dd,getdate())+1,getdate()),dateadd(dd,-1*datepart(dd,dateadd(mm,1,getdate())),dateadd(mm,1,getdate())))

选择first_day=dateadd(dd，-1*datepart(dd,getdate() +1,getdate()， last_day=dateadd(dd，-1*datepart(dd， -1,dateadd))，

replace any date with getdate to get the no of months in that particular date

用getdate替换任何日期，以获得该特定日期的月数

#20

DECLARE @Month INT=2,
    @Year INT=1989
DECLARE @date DateTime=null
SET @date=CAST(CAST(@Year AS nvarchar) + '-' + CAST(@Month AS nvarchar) + '-' + '1' AS DATETIME);

DECLARE @noofDays TINYINT 
DECLARE @CountForDate TINYINT
SET @noofDays = DATEPART(MONTH,@date )
SET @CountForDate = 28 + (@noofDays + floor(@noofDays/8)) % 2 + 2 %    @noofDays + 2 * floor(1/@noofDays)   
SET @noofDays= @CountForDate + CASE WHEN @CountForDate = 28 AND DATEPART(YEAR,@date)%4 =0 THEN 1 ELSE 0 END
PRINT @noofDays

#21

For any date

对于任何一个日期

select DateDiff(Day,@date,DateAdd(month,1,@date))

#22

DECLARE @date nvarchar(20)
SET @date ='2012-02-09 00:00:00'
SELECT DATEDIFF(day,cast(replace(cast(YEAR(@date) as char)+'-'+cast(MONTH(@date) as char)+'-01',' ','')+' 00:00:00' as datetime),dateadd(month,1,cast(replace(cast(YEAR(@date) as char)+'-'+cast(MONTH(@date) as char)+'-01',' ','')+' 00:00:00' as datetime)))

#23

simple query in SQLServer2012 :

SQLServer2012中的简单查询:

select day(('20-05-1951 22:00:00'))

选择一天((20-05-1951 22:00:00))

i tested for many dates and it return always a correct result

我测试了许多日期，它总是返回正确的结果

#24

DECLARE @date DATETIME = GETDATE();  --or '12/1/2018' (month/day/year)
SELECT EOMONTH ( @date ) AS 'This Month';
SELECT EOMONTH ( @date, 1 ) AS 'Next Month';

result: This Month 2018-12-31

结果:本月2018-12-31

Next Month 2019-01-31

下个月2019-01-31