tsql: How to retrieve the last date of each month

问题:

I have two date for example 08/08/2013 and 11/11/2013 and I need last date of each month starting from August to November in a table so that i can iterate over the table to pick those dates individually.

I know how to pick last date for any month but i am stucked with a date range.

kindly help, it will be highly appreciated.

Note : I am using Sql 2008 and date rang could be 1 month , 2 month or 6 month or a year or max too..

回答1:

You can use CTE for getting all last days of the month within the defined range

Declare @Start datetime
Declare @End datetime

Select @Start = '20130808'
Select @End = '20131111'
;With CTE as
(
Select @Start  as Date,Case When DatePart(mm,@Start)<>DatePart(mm,@Start+1) then 1 else 0 end as [Last]
UNION ALL
Select Date+1,Case When DatePart(mm,Date+1)<>DatePart(mm,Date+2) then 1 else 0 end from CTE
Where Date<@End
)

Select * from CTE
where [Last]=1   OPTION ( MAXRECURSION 0 )

回答2:

DECLARE @tmpTable table (LastDates DATE);
DECLARE @startDate DATE = '01/01/2012'; --1 Jan 2012
DECLARE @endDate DATE = '05/31/2012';   --31 May 2012
DECLARE @tmpEndDate DATE;

SET @startDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startDate)+1,1));
SET @tmpEndDate = DATEADD(DAY, 1, @endDate);

WHILE (@startDate <= @tmpEndDate)
BEGIN   
    INSERT INTO @tmpTable (LastDates) values (DATEADD(DAY, -1, @startDate));
    SET @startDate = DATEADD(MONTH, 1, @startDate);
END

SELECT [LastDates] FROM @tmpTable;

Output:

Example: 1

@startDate DATE = '01/01/2012'; --1 Jan 2012
@endDate DATE = '05/31/2012';   --31 May 2012

LastDates
----------
2012-01-31
2012-02-29
2012-03-31
2012-04-30
2012-05-31

Example: 2

@startDate DATE = '11/01/2011'; --1 Nov 2011
@endDate DATE = '03/13/2012';   --13 Mar 2012

LastDates
----------
2011-11-30
2011-12-31
2012-01-31
2012-02-29

回答3:

I've created a table variable, filled it with all days between @startDate and @endDate and searched for max date in the month.

declare @tmpTable table (dates date)
declare @startDate date = '08/08/2013'
declare @endDate date = '11/11/2013'

while @startDate <= @endDate
begin
    insert into @tmpTable (dates) values (@startDate)
    set @startDate = DATEADD(DAY, 1, @startDate)
end

select max(dates) as [Last day] from @tmpTable as o
group by datepart(YEAR, dates), datepart(MONTH, dates)

Results:

Last day
2013-08-31
2013-09-30
2013-10-31
2013-11-11

To also get last day of November this can be used before loop:

set @endDate = DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @endDate) + 1, 0))

回答4:

Following script demonstrates the script to find last day of previous, current and next month.

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
LastDay_NextMonth

If you want to find last day of month of any day specified use following script.

--Last Day of Any Month and Year
DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
LastDay_AnyMonth
ResultSet:
LastDay_AnyMonth

Source - SQL Server Central.

回答5:

You can use a recursive CTE to do this, note the MAXRECURSION OPTION prevents an infinite loop:

DECLARE @StartDate DATE = '2013-08-08'
DECLARE @EndDate DATE = '2013-11-11'

;WITH dateCTE
AS
(
    SELECT CAST(DATEADD(M, 1,DATEADD(d, DAY(@StartDate) * -1, @StartDate)) AS DATE) EndOFMonth
    UNION ALL 
    SELECT CAST(DATEADD(M, 2,DATEADD(d, DAY(EndOFMonth) * -1, EndOFMonth)) AS DATE) 
    FROM dateCTE
    WHERE EndOFMonth < DATEADD(d, DAY(@EndDate) * -1, @EndDate)

)
SELECT *
FROM dateCTE
OPTION (MAXRECURSION 30);

This returns

EndOFMonth
----------
2013-08-31
2013-09-30
2013-10-31

回答6:

try this the last row(where) is optional for date filtering

declare @table table  
(  
thisdate date  
)


insert into @table values ('12/01/2013'),('05/06/2013'),('04/29/2013'),('02/20/2013')
select *,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,thisdate)+1,0))
LastDay from @table
where thisdate between 'givendate' and 'givendate'

The Example Below is for all dates

thisdate    lastday
2013-12-01  2013-12-31 23:59:59.000
2013-05-06  2013-05-31 23:59:59.000
2013-04-29  2013-04-30 23:59:59.000
2013-02-20  2013-02-28 23:59:59.000

回答7:

The following CTE gives you the last day of every month from February 1900 until the middle of the 26th century (on my machine):

;with LastDaysOfMonths as (
    select DATEADD(month,
                 ROW_NUMBER() OVER (ORDER BY so.object_id),
                 '19000131') as Dt
    from sys.objects so,sys.objects so1
)
select * from LastDaysOfMonths

It should be easy enough to use it as part of a larger query or to filter it down to just the dates you want. You can adjust the range of years as needed by changing the constant 19000131. The only important thing to do is make sure that you use a month that has 31 days in it and always have the constant be for day 31.

回答8:

No need to use a common table expression or anything like that - this simple query will do it:

SELECT  DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(m, number, '2013-08-08')) + 1, 0)) AS EndOfMonth
FROM    master.dbo.spt_values
WHERE   'P' = type
        AND DATEADD(m, number, '2013-08-08') < '2013-11-11';

回答9:

Although the question is about the last day which @bummi has already answered.

But here is the solution for the first date which might be helpful for someone.

Get the first dates of all the months in-between the @FromDate and @ToDate.

DECLARE @FromDate DATETIME = '2019-08-13'
DECLARE @ToDate DATETIME = '2019-11-25'

;WITH CTE
AS 
(
    SELECT DATEADD(DAY, -(DAY(@FromDate) - 1), @FromDate) AS FirstDateOfMonth

    UNION ALL

    SELECT DATEADD(MONTH, 1, FirstDateOfMonth)
    FROM CTE
    WHERE FirstDateOfMonth < DATEADD(DAY, -(DAY(@ToDate) - 1), @ToDate)
)
SELECT * FROM CTE

Here is the result

--Result
2019-08-01 00:00:00.000
2019-09-01 00:00:00.000
2019-10-01 00:00:00.000
2019-11-01 00:00:00.000