How to get Month name and End Date of Month within a date rage

Question

In a given Date Range I would like to get Name of the Month and End Date of the month. Based on the given values, I will different counts and calculate the final value.

I can do it with CTE, But I dont have idea in how to it completely with CTE.

I prefer one more way than doing it using CTE.

Version of SQL 2008 R2, 2012.

I want the result for the complete date range I provide. Not for one single month

Example : Input which I provide is a date Range

Declare @Start datetime
Declare @End datetime

Select @Start = '2014-08-08'
Select @End = '2015-04-01'

Output required :

Month     End Date

August    2014-08-31 00:00:00.000

September 2014-09-30 00:00:00.000

October   2014-10-31 00:00:00.000

November  2014-11-30 00:00:00.000

December  2014-12-31 00:00:00.000

January   2015-01-31 00:00:00.000

February  2015-02-28 00:00:00.000

March     2015-03-31 00:00:00.000

Please clarify your question by proving sample data, input and expected results. — Felix Pamittan, Apr 15 '15 at 07:01
What version of SQLServer are you using? Can't you just use `MONTH(date)` for example? — NickyvV, Apr 15 '15 at 07:01
Why not add your CTE so we can see more clearly the issues you face — Ewan, Apr 15 '15 at 07:05
Clarified your questions and received the expected answer, Thank You — Seeker, Apr 15 '15 at 11:22

Maciej Nowicki · Accepted Answer · 2015-04-15T11:47:45.823

3

Works on SLQ SERVER 2008+

Declare @Start datetime Declare @End datetime

Select @Start = '2014-08-08' Select @End = '2015-04-01'
declare @day DATETIME = @Start

declare @tab TABLE(MONTH_NAME varchar(20),LAST_DAY_OF_MONTH DATETIME)

while @day <= @end
begin
    INSERT INTO 
        @tab
    SELECT
        DATENAME(month, @day),
        DATEADD(SECOND,-1,DATEADD(MONTH,1+ DATEDIFF(MONTH, 0, @day), 0))

    set @day = DATEADD(MONTH,1,@day)        
end

    SELECT
        *
    FROM
        @tab

edited Apr 15 '15 at 11:47

answered Apr 15 '15 at 07:15

Maciej Nowicki

237
1
13

I want for a complete date range not for one month. – Seeker Apr 15 '15 at 07:27
Can I get the time also. for example for august end date is 2014-08-31 23:59:59.000 – Seeker Apr 15 '15 at 11:32

sqluser · Answer 2 · 2015-04-15T08:00:44.300

For both 2012+ Or 2012-

DECLARE @Start DATETIME = '2014-08-08',
        @End DATETIME = '2015-04-01'


SELECT  DATENAME(MONTH, DATEADD(MONTH, a.number, @Start)) AS NameOfMonth
        ,EOMONTH(DATEADD(MONTH, a.number, @Start)) AS EndOfMonthForSQL2012Plus
        ,DATEADD(DAY, -1, DATEADD(MONTH, 1 + DATEDIFF(MONTH, 0, DATEADD(MONTH, a.number, @Start)), 0)) AS EndOfMonthForSQL2012Minus
FROM    master.dbo.spt_values a
WHERE   a.type = 'P'        
AND     a.number <= DATEDIFF(MONTH, @Start, @End);

score 1 · Answer 3 · edited May 23 '17 at 12:07

1

To get last date of the month:

Link 1

To get month name:

Link 2

Link 3

edited May 23 '17 at 12:07

Community

1
1

answered Apr 15 '15 at 08:49

Mahavirsinh Padhiyar

1,299
11
33

score 0 · Answer 4 · answered Apr 15 '15 at 08:29

I've used the following before. It takes a start date, but you'll need your own end date;

DECLARE @SeedDate DATETIME = '1 February 2015'

;WITH CalData AS (

SELECT DATENAME(MONTH, @SeedDate) AS Name,
       @SeedDate As StartDate,
       DATEADD(MONTH, ((YEAR(@SeedDate) - 1900) * 12) + MONTH(@SeedDate), -1) AS EndDate
UNION ALL
SELECT DATENAME(MONTH, DATEADD(MONTH, 1, StartDate)),
       DATEADD(MONTH, 1, StartDate), 
       DATEADD(MONTH, ((YEAR(StartDate) - 1900) * 12) + (MONTH(StartDate) + 1), -1)
FROM CalData

) SELECT TOP 200 * FROM CalData
OPTION (MAXRECURSION 5000);

How to get Month name and End Date of Month within a date rage

4 Answers4