This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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
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
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.
Joined: Jul 30, 2006
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)))