Month & Year comparison

Hi,

The problem here in hand is comparing the month & year ranges rather than the entire date. I am passing a start month and start year along with the end month and end year, now the query that i have written does not seem to be appropriate

My Query:

MONTH(Date_Added) BETWEEN @.StartMonth AND @.EndMonth

AND YEAR(Date_Added) BETWEEN @.StartYear AND @.EndYear

For some reason this works fine if i specify the startmonth as 1 and endmonth as 12, whereas if i specify 1 and 1 it doesnot work. Can anyone help me by refining my query so that i can search a particular date within mm/yyyy and mm/yyyy.

Thanks in advance.

Santosh

So if you put in between January and March and between 2004 and 2006, and then select for April 2005, it wouldn't qualify, right?

Do you really mean to select that way, or do you want to select between January 2004 and March 2006?

If you want to select continuous dates, set your first date to the first day of the month and your last date to the last day of the month and then search between those two values.

Here's a function to set a date to the first day of the month. You can use the same logic to set a date to the last day of the month.

http://www.sql-server-helper.com/functions/get-first-day-of-month.aspx

|||I would like to select between January 2004 and March 2006?|||

That'swhat I figured you want. You can't unlink the logic. Basically you have to have your year/months combined into a single variable and then do a between for those dates. Set the earlier date to the first of the month and your later date to the end of the month.

|||

WHERE Date_Added >= CAST(CAST(@.StartYear AS VARCHAR(4)) + '/' + CAST(@.StartMonth AS VARCHAR(2)) + '/01' AS DATETIME) AND Date_Added < DATEADD(month,1,CAST(CAST(@.EndYear AS VARCHAR(4)) + '/' + CAST(@.EndMonth AS VARCHAR(2)) + '/01' AS DATETIME))

|||

Yeah, that's the idea.