SQL Server 2008 select data only between month and year

Question

I would like select data between two date, without day

An input example:

start month: 9 , start year: 2011
end month: 3, end year: 2012

I think that there are two way to do this.

The first is convert start month and start year to date like 2011-09-01 and convert last date to 2012-03-31, but this requires calculation of the last day of end month. Obtained these date we can use a BEETWEN function for the WHERE clause (but, is the CONVERT function reliable?)

The second solution is to use the DATEPART function like in the following code:

I try to explain: if end year is equal to the initial year, then month must be between the start and end months; else if the final months is greater than the initial years if different from the initial and final year, I take everything in between; else if the final year, the month must be less than or equal to the final month, if the initial year, month must be greater than or equal to the final month

Can you help me do this in the best way? Is correct, the solution I adopted?

declare @IndDebitoCredito bit,@ProgTributo int,@mi as integer,@ai as integer,@mf as integer,@af as integer,@IDAnagrafica varchar(5)
select @mi = 01,@ai = 2011,@mf = 12,@af = 2011,@IDAnagrafica = 'DELEL',@IndDebitoCredito = 1
select distinct rrd.IDTributo
    from TBWH_Delega d
    --inner join TBWH_SezioneDelega sd on d.IDDelega = sd.IDDelega
    inner join TBWH_Rigo rd on rd.IDDelega = d.IDDelega
    inner join TBWH_RataRigo rrd on rrd.IDRigo = rd.IDRigo 
    where
    (
        DATEPART(MM,d.DataDelega)<=@mf and 
        DATEPART(MM,d.DataDelega)>=@mi and 
        DATEPART(YYYY,d.DataDelega)=@ai and 
        @af = @ai
    ) 
    OR
    (
        --anno finale magg. anno iniziale
        @af > @ai AND
        ( 
            (   -- delega nell'intervallo
                DATEPART(YYYY,d.DataDelega)<@af AND 
                DATEPART(YYYY,d.DataDelega)>@ai
                -- DATEPART(MM,d.DataDelega)>=@mi 
            )
            OR
            (   -- delega limite destro
                DATEPART(YYYY,d.DataDelega)=@af AND 
                DATEPART(MM,d.DataDelega)<=@mf 
            )
            OR
            (   -- delega limite sinistro
                DATEPART(YYYY,d.DataDelega)=@ai AND 
                DATEPART(MM,d.DataDelega)>=@mi 
            )
        )
    )

GO

Answer 1

I would use:

WHERE DateToCheck >=               --- first day of StartMonth
          DATEADD( mm, @StartMonth-1,
                   DATEADD( yy, @StartYear-2000, '2000-01-01') 
                 )
  AND DateToCheck <                --- first day of next month (after EndMonth)
          DATEADD( mm, @EndMonth,
                   DATEADD( yy, @EndYear-2000, '2000-01-01') 
                 )

Answer 2

DECLARE @mi INT
  , @ai INT
  , @mf INT
  , @af INT
SELECT  @mi = 01
      , @ai = 2011
      , @mf = 12
      , @af = 2011

--local variables to hold dates
DECLARE @i DATETIME
  , @f DATETIME

--build strings to represent dates in YYYYMMDD format
--add a month to the @f date
SELECT  @i = CONVERT(VARCHAR(4), @ai) + RIGHT('0' + CONVERT(VARCHAR(2), @mi),
                                              2) + '01'
      , @f = DATEADD(month, 1,
                     CONVERT(VARCHAR(4), @af) + RIGHT('0'
                                                      + CONVERT(VARCHAR(2), @mf),
                                                      2) + '01')

--select data where date >= @i, and < @f
SELECT  *
FROM    MyTable
WHERE   DateField >= @i
        AND DateField < @f 

Answer 3

You are WAY overcomplicating this. You really only need two comparisons:

• Is the month and year after or equal to the initial value?
• Is the month and year before or equal to the final value?

Try:

SELECT *
FROM MyTable
WHERE Datefield BETWEEN 
     CAST(@mi as varchar) + '/1/' + CAST(@ai as varchar) 
     -- first of first month
     AND
     DATEADD(DAY, -1, (DATEADD(Month, + 1, (CAST(@mf as varchar) + '/1/' + CAST(@af as varchar))))) 
     -- Last day or final month

Answer 4

SELECT *
  FROM Table
 WHERE DateField
       BETWEEN CONVERT(DATE, CONVERT(CHAR(4), @ai) + RIGHT('00' + CONVERT(VARCHAR(2), @mi), 2) + '01', 112)
           AND DATEADD(DD, -1, DATEADD(MM, 1, CONVERT(DATE, CONVERT(CHAR(4), @af) + RIGHT('00' + CONVERT(VARCHAR(2), @mf), 2) + '01', 112)))

Avoid using expressions on the DateField columns, as it makes query not SARGable.

Answer 5

Your first solution is almost there, but is more complicated than it needs to be and won't work anyway. It will miss out any rows from the last day of the end month.

You can add one month to the end month and then use BETWEEN on the first of each month. eg.

start month: 9 , start year: 2011 end month: 3, end year: 2012

BETWEEN '2011-09-01' AND '2012-04-01'

or, as JNK points out, this will be better:

DataDelega >= '2011-09-01' AND DataDelega < '2012-04-01'

You'll need to add in some logic to deal with the end month being December, but this looks like the simplest way of doing it.