Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

having problem with a stored procedure used called by a DAO

 
huzefa qutbuddin
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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é
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic