aspose file tools*
The moose likes JDBC and the fly likes having problem with a stored procedure used called by a DAO Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "having problem with a stored procedure used called by a DAO" Watch "having problem with a stored procedure used called by a DAO" New topic
Author

having problem with a stored procedure used called by a DAO

huzefa qutbuddin
Greenhorn

Joined: Jul 30, 2006
Posts: 6
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

Joined: Nov 24, 2005
Posts: 14688
    
  16

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.


[My Blog]
All roads lead to JavaRanch
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

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.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Binu Sen
Greenhorn

Joined: Jul 31, 2006
Posts: 2
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

Joined: Jul 30, 2006
Posts: 6
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: having problem with a stored procedure used called by a DAO