having problem with a stored procedure used called by a DAO

[huzefa qutbuddin]

hi guys this question is actually about MSSQL but I wasn't sure where to post it

I have a stored procedure in which I need to get all the classes which are taking place in a particular month

for eg

If I pass August 2006
it should get me all the classes which for eg start and end at these dates
class1 from june 2006 - december 2006
class2 from August 2006 - August 2006
class3 from january 2006 - september 2006

All class records have start_date and end_date fields.

I use the query below
-------------------------------------------------------------------
CREATE PROCEDURE ccim_getClasses ( @class_id varchar(8), @month varchar (8), @year varchar(8)) AS

select
classID
.
.
.
FROM classes
where classes.CLASS = @class_id and
convert(datetime, start_date, 101) <= CONVERT(DATETIME, CAST(@month AS VARCHAR(2))+'/31/'+CAST(@Year AS VARCHAR(4)), 101) AND
convert(datetime, end_date, 101) >=CONVERT(DATETIME, CAST(@month AS VARCHAR(2))+'/01/'+CAST(@Year AS VARCHAR(4)), 101)
GO

now the problem is that as i am using

start_date <= month/31/year
all months don't have 31 days
so what I was thinking is to make the date also a variable and assign a value based on month
if january then number_of_days = this
would anyone know how to implement if statement in the query
or if there is a better option

Please if anyone can help me on this. Thanks

[Christophe Verré]

start_date <= month/31/year
all months don't have 31 days

Is this important ?
With this condition, you'll get all records for each month, whether they have 31 days or not.

[Ben Souther]

Originally posted by huzefa qutbuddin:
hi guys this question is actually about MSSQL but I wasn't sure where to post it

The best place, on this site, for any database related questions is the JDBC forum.
I'll move this thread over there for you.

[Binu Sen]

I think you may use datepart function to get year and month of start & end date and match with the given month and year. This will help you for any month and year. In datepart month and year both comes as integer so "between" will be easy.

[huzefa qutbuddin]

I used the dateadd function to get the last day of the month and it worked

convert(datetime, event_date, 101) <=DATEADD(day, -1, DATEADD(month, 1, CONVERT(DATETIME, CAST(@month AS VARCHAR(2))+'/01/'+CAST(@Year AS VARCHAR(4)), 101)))

Thnaks again guys