15,904,153 members
See more:
Hi,

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

## Solution 2

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
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');`

## Solution 3

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
END

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

RETURN @num
END```

ssd_coolguy 21-Jul-12 3:30am
my 5!...

## Solution 1

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

v2
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...