CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Master Yoda
Master Yoda

Reputation: 4402

TSQL - Getting all results between month/year range

I have a stored procedure that accepts the following parameters:

  1. Month From
  2. Year From
  3. Month To
  4. Year To

Right now my query looks like this:

SELECT      COL1, MyDate
FROM        [dbo].[MyTable]
WHERE       MONTH([MyDate]) >= @fromMonth
AND         MONTH([MyDate]) <= @toMonth
AND         YEAR([MyDate])  >= @fromYear
AND         YEAR([MyDate])  <= @toYear
GROUP BY    COL1, MyDate

But If I run the query with the parameters:

  1. Month From = 9
  2. Year From = 2016
  3. Month To = 3
  4. Year To = 2017

I get no results even if i have results that should be returned.

Upvotes: 0

Views: 89

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

For sql server version 2012+, you can use DATEFROMPARTS and EOMONTH:

DECLARE @FromDate datetime, @ToDate datetime

SELECT  @FromDate = DATEFROMPARTS(@fromYear, @FromMonth, 1),
        @ToDate =  EOMONTH(DATEFROMPARTS(@ToYear, @ToMonth, 1)),

SELECT      COL1, MyDate
FROM        [dbo].[MyTable]
WHERE       [MyDate] >= @FromDate
AND         [MyDate] <= @ToDate
GROUP BY    COL1, MyDate

For versions before 2012, you can constract the date with a little math and casting:

DECLARE @FromDate datetime, @ToDate datetime
SELECT  @FromDate = CAST(CAST(@FromYear * 10000 + @FromMonth * 100 + 1 as char(8)) as date),
        @ToDate =  DATEADD(MONTH, cast(CAST(@ToYear * 10000 + @ToMonth * 100 + 1 as char(8)) as date))
SELECT      COL1, MyDate
FROM        [dbo].[MyTable]
WHERE       [MyDate] >= @FromDate
AND         [MyDate] < @ToDate
GROUP BY    COL1, MyDate

Note the use of DateAdd to get the first day of the month after the @ToDate and using < and not <=.

Upvotes: 4

Cameron
Cameron

Reputation: 2594

Since, you didn't give us some sample data from your table, I generated my own.

CREATE TABLE #Temp
    (
      MyCol VARCHAR(10)
    , MyDate DATETIME
    );
INSERT  INTO #Temp
        ( MyCol, MyDate )
VALUES  ( 'A', GETDATE() ),
        ( 'A', '2016-09-01' ),
        ( 'B', '2016-09-03' ),
        ( 'B', '2016-08-28' ),
        ( 'A', '2017-03-01' ),
        ( 'A', '2017-04-01' );

Let's take one date (2016-09-01) and break it down.

Your WHERE clause is effectively testing the following conditions for the first row.

  1. 9 >= 9 => True
  2. 9 <= 3 => False
  3. 2016 >= 2016 => True
  4. 2016 <= 2017 => True

Because you're AND-ing all of these conditions, you're not returning the results you're wanting.

You need to evaluate your values as dates rather than numbers, otherwise you'll encounter these issues.

You could do something like this:

DECLARE @ToDate AS DATE = CONVERT(DATE, CONVERT(VARCHAR, @toYear) + '-' + CONVERT(VARCHAR, @toMonth) + '-1');
DECLARE @FromDate AS DATE = CONVERT(DATE, CONVERT(VARCHAR, @fromYear) + '-' + CONVERT(VARCHAR, @fromMonth) + '-1');

SELECT  t.MyCol
      , t.MyDate
FROM    #Temp AS t
WHERE   t.MyDate BETWEEN @FromDate AND @ToDate
GROUP BY t.MyCol
      , t.MyDate;

This is a good starting point, but given your provided variables, you'll only get data from 2016-09-01 to 2017-04-01 rather than 2016-09-01 to 2017-03-31.

So you'll need to add an additional month. You can do this a variety of ways (Add one to the month and check to see if it's over 13, DATEADD, etc.)

I chose DATEADD because it's a little simpler.

Your end result comes up like this:

DECLARE @ToDate AS DATE = DATEADD(MONTH, 1, CONVERT(DATE, CONVERT(VARCHAR, @toYear) + '-' + CONVERT(VARCHAR, @toMonth) + '-1'));
DECLARE @FromDate AS DATE = CONVERT(DATE, CONVERT(VARCHAR, @fromYear) + '-' + CONVERT(VARCHAR, @fromMonth) + '-1');

SELECT  t.MyCol
      , t.MyDate
FROM    #Temp AS t
WHERE   t.MyDate >= @FromDate AND
        t.MyDate < @ToDate
GROUP BY t.MyCol
      , t.MyDate;

Edit: There's probably a cleaner way to generate a date than doing concatenation and converting.

Upvotes: 0

Related Questions