Finding specific days between two dates

Question

I need help to find out how many Fridays there are between two specific dates. I have found and read through the post concerning; Work days between two dates - but I'm not able to crack how to count specific days between two dates. I need a more reliable solution than just dividing it by 7.

Thanks!

You should tag your question with the database you are using. — Gordon Linoff, Feb 28 '17 at 11:57
And also define “a more reliable solution than just dividing it by 7”. As long as a week has seven days, that number is going to come into play in one way or another. — CBroe, Feb 28 '17 at 11:59
@jarlh that could be "one way" or the "other way" CBore is talking about. — Ejaz, Feb 28 '17 at 12:01
@GordonLinoff Sorry, I have added this. Thanks for your reply — DALLAS, Feb 28 '17 at 12:01
@Ejaz I have only been trying around with the code from the link I posted, so I don't have any code to post at this moment sorry. — DALLAS, Feb 28 '17 at 12:04
Would following method work? `if first day of interval is friday, then, number_of_fridays = 1 ; number_of_fridays += integer value of (number of days in interval % 7);`?... or gives some direction? — Ejaz, Feb 28 '17 at 12:08
@jarlh: Yes, of course. What day of week the start or end date (depending which side you start) actually is, has to be taken into account of course. — CBroe, Feb 28 '17 at 12:08

score 0 · Accepted Answer · answered Feb 28 '17 at 12:10

0

declare @startDate date;
declare @endDate date;

select @startDate = '2017-02-01';
select @endDate = '2017-02-10';

with dateBetween as
(
  select dt = dateadd(dd, 1, @startDate)
  where dateadd(dd, 1, @startDate) < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateBetween
  where dateadd(dd,1, dt) < @endDate
)

select DATENAME(dw,dt) As [Dayname],dt as [Date]
from dateBetween where DATENAME(dw,dt) ='Friday'

answered Feb 28 '17 at 12:10

Singh Kailash

621
5
16

Thanks a million Singh, perfect! – DALLAS Feb 28 '17 at 12:13
I added [option (maxrecursion 0)] at the end to remove the maximum recursion error message. – DALLAS Feb 28 '17 at 12:19
try option (maxrecursion 365) – Singh Kailash Feb 28 '17 at 12:24
My startDate and endDate are 14 years apart, so that won't work. – DALLAS Feb 28 '17 at 12:26
try Different CTE for each year and save in Temp Or Dynamic table then finally use (Temp Or Dynamic table) For insert – Singh Kailash Feb 28 '17 at 12:28
This is an awful solution, you only need some basic maths, as per the duplicate... – DavidG Feb 28 '17 at 12:33
@DavidG save dates in Dynamic table also so how can retrive duplicate data, can you suggest another solutions – Singh Kailash Feb 28 '17 at 12:36
Yes, the [duplicate](http://stackoverflow.com/questions/7563069/how-to-calculate-the-number-of-tuesdays-between-two-dates-in-tsql) this question was closed against. – DavidG Feb 28 '17 at 12:37
@DavidG can you suggest another solutions.... – Singh Kailash Feb 28 '17 at 12:38
Why would I want to suggest other solutions when a very simple and effective one already exists? – DavidG Feb 28 '17 at 12:39
solutions without CTE because some old db version CTE Not working.. – Singh Kailash Feb 28 '17 at 12:41
YES! The duplicate I linked!!! – DavidG Feb 28 '17 at 12:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/136896/discussion-between-singh-kailash-and-davidg). – Singh Kailash Feb 28 '17 at 18:04

Finding specific days between two dates

1 Answers1