CODEWITHSUNDEEP
sqlsql-serversql-server-2008date
Chester van Ree
Chester van Ree

Reputation: 105

How can i get the First Date and Last Date between selected months?

How can i get the First Date and Last Date between selected months?

For example I Enter year 2015, and month 09 to 2016 08

The return values must be: 

StartDate EndDate   
20150901 20150930  
20151001 20151031  
20151101 20151130  
20151201 20151231  
20160101 20160131  
20160201 20160229  
20160301 20160331   
20160401 20160430      
20160501 20160531   
20160601 20160630   
20160701 20160731   
20160801 20160831

I've found multiple queries that do the same but just for 1 inserted date. for example:

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
'Last Day of Current Month'

But I couldn't find one that can do this for Between statement

Upvotes: 0

Views: 816

Answers (4)

mvisser
mvisser

Reputation: 670

Try this. Its also a very small script. I like the idea of the CTE from Siyual. But this is much shorter.

DECLARE @inputYear INT = 2015,
@inputMonth INT = 09

SELECT DATEADD(MONTH, @inputMonth-1, DATEADD(YEAR, @inputYear-1900, 0)) AS FirstDayOfTheMonth,
DATEADD(s,-1, DATEADD(MONTH, @inputMonth, DATEADD(YEAR, @inputYear-1900, 0))) AS LastDayOfTheMonth

Upvotes: 0

Samuel Renold
Samuel Renold

Reputation: 302

Your task is also possible using one single ANSI sql statement. However, you need two tables ALL_YEARS and ALL_MONTHS that contain the used set of years and month.

E.g. if you consider possible years between 1900 and 2099, the table ALL_YEARS must contain the numbers between 1900 and 2099. The table ALL_MONTHS must contain the numbers between 1 and 12.

Using these two tables, the statement looks as follows:

select            to_date('01.' || to_char(all_months.month_value) || '.' || to_char(all_years.year_value ), 'dd.mm.yyyy') first_day ,
       add_months(to_date('01.' || to_char(all_months.month_value) || '.' || to_char(all_years.year_value ), 'dd.mm.yyyy'), 1) -1 last_day
from   all_years,
       all_months
where  to_date('01.' || to_char(all_months.month_value) || '.' || to_char(all_years.year_value ), 'dd.mm.yyyy') between 
        to_date('01.02.2014', 'dd.mm.yyyy') and 
        to_date('01.07.2016', 'dd.mm.yyyy');

The output is as follows:

01.02.14    28.02.14
01.03.14    31.03.14
01.04.14    30.04.14
01.05.14    31.05.14
01.06.14    30.06.14
...
01.06.16    30.06.16
01.07.16    31.07.16

Upvotes: -1

Siyual
Siyual

Reputation: 16917

You don't need UDFs for this - You can do this with a single query:

Declare @FromYear   Int = 2015,
        @FromMonth  Int = 9,
        @ToYear     Int = 2016,
        @ToMonth    Int = 8

Declare @FromDate   Date = (Select DateAdd(Year, @FromYear - 1900, DateAdd(Month,  @FromMonth - 1, 0))),
        @ToDate     Date = (Select DateAdd(Year, @ToYear - 1900, DateAdd(Month, @ToMonth - 1, 0)))

;With Date (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Month, 1, Date)
    From    Date
    Where   Date < @ToDate
)
Select  Convert(Varchar, Year(Date)) + Right('00' + Convert(Varchar, Month(Date)), 2) + Right('00' + Convert(Varchar, Day(Date)), 2)    As StartDate,
        Convert(Varchar, Year(Date)) + Right('00' + Convert(Varchar, Month(Date)), 2) + Right('00' + Convert(Varchar, Day(DateAdd(Day, -1, DateAdd(Month, DateDiff(Month, 0, Date) + 1, 0)))), 2)   As EndDate 
From    Date
Option  (MaxRecursion 0)

Output

StartDate   EndDate
20150901    20150930
20151001    20151031
20151101    20151130
20151201    20151231
20160101    20160131
20160201    20160229
20160301    20160331
20160401    20160430
20160501    20160531
20160601    20160630
20160701    20160731
20160801    20160831

If you're able to move to SQL Server 2012 or later, however, the query is much simpler:

Declare @FromYear   Int = 2015,
        @FromMonth  Int = 9,
        @ToYear     Int = 2016,
        @ToMonth    Int = 8

Declare @FromDate   Date    = DateFromParts(@FromYear, @FromMonth, 1),
        @ToDate     Date    = DateFromParts(@ToYear, @ToMonth, 1)

;With Date (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Month, 1, Date)
    From    Date
    Where   Date < @ToDate
)
Select  Format(Date, N'yyyyMMdd')           As StartDate, 
        Format(EoMonth(Date), N'yyyyMMdd')  As EndDate
From    Date
Option  (MaxRecursion 0)

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81990

I have two udf's which can be used independently or in conjunction. They dynamically create date ranges with variable dateparts and/or increments. 

Select * from [dbo].[udf-Create-Range-Date-Span]('2015-09-01','2016-08-01','MM',1) 

DateR1                  DateR2
2015-09-01 00:00:00.000 2015-10-01 00:00:00.000
2015-10-01 00:00:00.000 2015-11-01 00:00:00.000
2015-11-01 00:00:00.000 2015-12-01 00:00:00.000
2015-12-01 00:00:00.000 2016-01-01 00:00:00.000
2016-01-01 00:00:00.000 2016-02-01 00:00:00.000
2016-02-01 00:00:00.000 2016-03-01 00:00:00.000
2016-03-01 00:00:00.000 2016-04-01 00:00:00.000
2016-04-01 00:00:00.000 2016-05-01 00:00:00.000
2016-05-01 00:00:00.000 2016-06-01 00:00:00.000
2016-06-01 00:00:00.000 2016-07-01 00:00:00.000
2016-07-01 00:00:00.000 2016-08-01 00:00:00.000

Now, I specifically use the cap so I can query data (including time) which are between the DateR1 and DateR2 and

However, with a little twist, you can change the output. For example:

Select DateR1=cast(DateR1 as Date),DateR2=DateAdd(DD,-1,cast(DateR2 as Date)) from [dbo].[udf-Create-Range-Date-Span]('2015-09-01','2016-08-01','MM',1) 

DateR1      DateR2
2015-09-01  2015-09-30
2015-10-01  2015-10-31
2015-11-01  2015-11-30
2015-12-01  2015-12-31
2016-01-01  2016-01-31
2016-02-01  2016-02-29
2016-03-01  2016-03-31
2016-04-01  2016-04-30
2016-05-01  2016-05-31
2016-06-01  2016-06-30
2016-07-01  2016-07-31

The two function are as follows:

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

AND

CREATE FUNCTION [dbo].[udf-Create-Range-Date-Span] (@Date1 datetime,@Date2 datetime,@DatePart varchar(10),@Incr int)

-- Syntax Select * from [dbo].[udf-Create-Range-Date-Span]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date-Span]('2016-01-01','2016-12-31','MM',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date-Span]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date-Span]('2016-10-01','2016-10-02','SS',1) 

Returns Table 

As
Return (

     Select DateR1 = RetVal
           ,DateR2 = LEAD(RetVal,1,@Date2) OVER (ORDER BY RetVal)
     From (Select * from [dbo].[udf-Create-Range-Date](@Date1,@Date2,@DatePart,@Incr) ) A
     Where RetVal<@Date2

)

Upvotes: 1

Related Questions