Get the list of days in the current month

Keywords: SQL Server SQL

To realize this function, please refer to the following articles< T-SQL get February days>https://www.cnblogs.com/insus/archive/2011/04/22/2025019.html

<How to get the days of a month>https://www.cnblogs.com/insus/archive/2011/09/10/2173028.html

<Gets the first day of the month in which the specified date is located>https://www.cnblogs.com/insus/archive/2011/09/11/2173612.html

You can write a table valued functions:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-09
-- Update date: 2019-05-09
-- Description: Get all dates of the specified month
-- =============================================

CREATE FUNCTION [dbo].[tvf_DaysOfMonth]
(
    @InputDate DATETIME
)
RETURNS @dump TABLE
(
    [Date] DATETIME
)
AS
BEGIN    
   DECLARE @firstDayOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,@InputDate),0) --Get the first day of the month

    DECLARE @daysOfMonth INT =  DAY(DATEADD(DAY,-1, DATEADD(MONTH,1,@firstDayOfMonth)))  --Get the number of days in the month

    INSERT INTO @dump ([Date]) VALUES(@firstDayOfMonth) --Insert the first day into the table.

    DECLARE @d INT = 1
    WHILE @d < @daysOfMonth
    BEGIN
        INSERT INTO @dump ([Date]) VALUES(@firstDayOfMonth + @d)
        SET @d = @d + 1
    END
    RETURN
END

Source Code

Example:

If you want more writing references, Insus.NET has changed the above custom function. First, get the first day of the month in which the specified date is located, and then get the first day of the month next to the specified date.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-09
-- Update date: 2019-05-09
-- Description: Get all dates of the specified month
-- =============================================

CREATE FUNCTION [dbo].[tvf_DaysOfMonth]
(
    @InputDate DATETIME
)
RETURNS @dump TABLE
(
    [Date] DATETIME
)
AS
BEGIN    
   DECLARE @firstDayOfMonth DATETIME = DATEADD(MONTH,DATEDIFF(MONTH,0,@InputDate),0) --Get the first day of the specified month
   DECLARE @firstDayOfNexMonth DATETIME = DATEADD(MONTH,1,@firstDayOfMonth) --Gets the first day of the next month in the specified month

    DECLARE @dumpDate DATETIME = @firstDayOfMonth

    WHILE (@dumpDate < @firstDayOfNexMonth)
    BEGIN        
        INSERT INTO @dump ([Date]) VALUES(@dumpDate)        
        SET @dumpDate = @dumpDate + 1
    END    
    RETURN
END

Source Code

Programmer Group

Get the list of days in the current month

Hot Keywords