Click here to Skip to main content
15,890,399 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Declare 
 @counter int = 0,
 @strt date,
 @end date
Begin 
set @strt=(Select Distinct StartDate
		   From SchoolTerms
		   where EmisCode ='500226884' 
		   AND CurrentYear='2009'
		   AND Quater='Term1')
Set @end=(Select Distinct EndDate
		   From SchoolTerms
		   where EmisCode ='500226884' 
		   AND CurrentYear='2009'
		   AND Quater='Term1')
while (@strt<@end)
if DATEPART(weekday,@strt)<>7 or DATEPART(weekday,@strt)<> 1 
	set @counter+=1
	set @strt=@strt-1
print @strt
print @end
print @counter
End;


i've got the following code for calculating total number of days in a term but i get the following error.

Msg 206, Level 16, State 2, Line 19
Operand type clash: date is incompatible with int
Posted

SQL
set @strt=@strt-1
is causing the problem because you are try to subtract 1 from a date. Use DATEADD[^] if you want to subtract one day.

Have a look at DATEDIF[^] for a better way to calculate the number of days.
 
Share this answer
 
Comments
SandraCy 30-Sep-11 3:35am    
i've used set @strt=DATEADD(day,1,@strt) but still it includes dates
SandraCy 30-Sep-11 3:36am    
it includes weekends i mean
André Kraak 30-Sep-11 13:43pm    
Have a look at this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698 for possible solutions.
The best way to do it would be creating a calendar table (you will be able to track holidays too).
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900