Click here to Skip to main content
15,904,153 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

how to get the number of Saturdays in given two dates in sql server.
Posted

Make Scaler Valued Function as
SQL
ALTER FUNCTION [dbo].[WeekdaysInInterval]
( @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @day_of_week VARCHAR(9) )
RETURNS INT
AS

BEGIN
DECLARE @found BIT, @num INT
SET @found = 0

--find first occurence of day of week in interval
WHILE @found = 0 AND (@StartDate < @EndDate)
BEGIN
IF DATENAME(weekday,@StartDate) = @day_of_week 
BEGIN SET @found = 1 END
ELSE	
SET @StartDate = DATEADD(day,1,@StartDate)
END

--find number of weeks with this day of week included
SET @num = (DATEDIFF(day,@StartDate, @EndDate) / 7) + 1

RETURN @num
END


and use as
SQL
select dbo.WeekdaysInInterval('02/05/2012','02/25/2012','saturday');
 
Share this answer
 
This is not my function.Its taken from above answer of Uma Shankar Patel.
But It have a small problem.This problem occurence when at the last @found =0

Rectified problem is below

SQL
ALTER FUNCTION [dbo].[WeekdaysInInterval]
( @StartDate SMALLDATETIME, @EndDate SMALLDATETIME, @day_of_week VARCHAR(9) )
RETURNS INT
AS
 
BEGIN
DECLARE @found INT, @num INT
SET @found = 0
 
--find first occurence of day of week in interval
WHILE @found = 0 AND (@StartDate < @EndDate)
BEGIN
IF DATENAME(weekday,@StartDate) = @day_of_week 
BEGIN SET @found = 1 END
ELSE	
SET @StartDate = DATEADD(day,1,@StartDate)
END
 
--find number of weeks with this day of week included
SET @num = (DATEDIFF(day,@StartDate, @EndDate) / 7) + @found
 
RETURN @num
END
 
Share this answer
 
Comments
ssd_coolguy 21-Jul-12 3:30am    
my 5!...
SQL
select (datediff(day,'2012-07-01','2012-07-22')/7)+1  as Saturday
 
Share this answer
 
v2
Comments
ssd_coolguy 21-Jul-12 1:55am    
it will not return correct answer..
Rakshith Kumar 8-Oct-13 1:59am    
It will not return a proper answer
Vasim889 21-Jul-12 2:09am    
k.you will try this one
ssd_coolguy 21-Jul-12 3:30am    
but see below query which returns incorrect result.

select (datediff(day,'2012-07-01','2012-07-06')/7)+1 as Saturday

it should be return 0 na??
Gssankar 24-Jul-12 11:52am    
Thanks it works...

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