Get number of saturdays in given dates in sql server

Question

0.00/5 (No votes)

See more:

SQL

Hi,

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

Posted 20-Jul-12 19:12pm

Gssankar

Add a Solution

3 solutions

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

Posted 20-Jul-12 19:49pm

uspatel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Kaushik Saha from Kolkata,India · Accepted Answer · 2012-07-20T21:20:00

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

Vasim889 · Accepted Answer · 2012-07-20T19:48:00

Solution 1

SQL

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

Posted 20-Jul-12 19:48pm

Vasim889

Updated 20-Jul-12 20:08pm

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