Check date interval with SQL Server

Question

Hi in a stored procedure I want to select rows that are in an interval of a month where @Anno is the year of the start and @Mese is the month of the date start. I try this:

WHERE FinePeriodo >= CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime)
    AND FinePeriodo < 
           DATEADD(month, 10, CAST(
               (@Anno + '-' + @Mese + '-01 00:00:00') as datetime))

But it doesn't work.

How can I do?

thanks

Answer 1

First of all, in order to make sure your date arithmetic works regardless of the language and regional settings on your server, use the ISO-8601 format YYYYMMDD for your dates.

In that case, you should be able to do something like:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

-- this assume @Anno and @Mese are already VARCHAR variables!
SET @StartDate = CAST(@Anno + RIGHT('00'+@Mese, 2) + '01' AS DATETIME)
SET @EndDate = DATEADD(MONTH, 1, @StartDate)

and then in your WHERE clause, use:

WHERE FinePeriodo BETWEEN @StartDate AND @EndDate

Could that work for you?

Answer 2

The code below works:

declare @anno VARCHAR(4)
declare @mese VARCHAR(2)

SET @Anno = '2006'
SET @mese = '6'

SELECT CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime) as BeginMOnth,
    DATEADD(month,10,CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime)) as EndMonth

select [name],crDate from sysobjects
WHERE crDate >= CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime)
          AND crDate < DATEADD(month,10,CAST((@Anno + '-' + @Mese + '-01 00:00:00') as datetime))

Using sysobjects table for test.

• Is your search field an actual date (not a character type instead)
• Is it possible you've got extra spaces, etc in your variables
• Do you get an error or just the wrong records back

Answer 3

Try this: (make @Anno and @Mese integers)

  Where FinePeriodo Between DateAdd(month, 12*(@Anno-1900) + @Mese, 0)
                       And  DateAdd(month, 12*(@Anno-1900) + @Mese + 1, 0)