Please Help me!!!

Question

Please Help me!!!

Anupama

Valued Member

Points: 64
More actions
July 23, 2003 at 2:01 am

#80909

Hi All,
Please help me.
Say you have 2 dates i.e 01-Jun-2003' and 30-Jun-2003'. I need to find how many fridays exists between these 2 dates in a single query.
Thanks,
Anu

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bond007 SSCrazy Points: 2056 More actions · Answer 1

This looks a little messy and can probably be tidied up alot but it will hopefully get you started.

 declare @start datetime, @end datetime

declare @i int, @a int, @count int

Declare @name varchar(10), @Day varchar(10)

set @start = convert(datetime,'01/06/2003',103)

set @end = convert(datetime,'10/06/2003',103)

set @day = 'Friday'



select @i = datediff(d,@start,@end)

set @a = 1

set @count = 0



While @a <= @i+1

Begin



Set @name = datename(dw,@start)



If @name = @Day



Set @count = @count + 1

Set @start = DateAdd(d,1,@start)

Set @a = @a + 1



End

select @count

Growing old is mandatory, growing up is optional

NPeeters SSChampion Points: 12536 More actions · Answer 2

Calculate the number of days between them and divide that number by 7.

Next, you will have to do some additional math to check if your interval starts / ends on a friday.



SELECT (DATEDIFF(d, <startdate>, <enddate>)/7)

    + CASE WHEN DATEPART(dw, <startdate>) = 6

             OR DATEPART(dw, <enddate>)   = 6

           THEN 1

           ELSE 0

      END

Edited by - NPeeters on 07/23/2003 03:15:10 AM

bond007 SSCrazy Points: 2056 More actions · Answer 3

Much, much nicer. Told you. 🙂

Growing old is mandatory, growing up is optional

guarddata SSCertifiable Points: 6583 More actions · Answer 4

Almost - but test that with July 10 - July 19 and the function fails to yield 2.

Try this one instead.

select ((1+datediff(d, <StartDate>, <EndDate>)+datepart( dw, <StartDate> ) ) / 7 )

- case when datepart( dw, <StartDate> ) = 7 then 1 else 0 end

Guarddata-

ymiossec Newbie Points: 1 More actions · Answer 5

You can do it in a single select by using SQL Server ability to count Weeks.

You only have to subtract from your two dates the 'dayofweek' value of the day of week ou want to count :

if you have for example @start and @end dates, use a @dow to store the day of week you want to count.

I think it's 6 for friday if you use a US SQL Server (5 for French install).

So, you can use :

select datediff(week, dateadd(second,-1,dateadd(day, @dow*-1, @Start)), dateadd(day, @dow*-1, @end))

The subtract of one second on the first date is necessary because the datediff function counts the boundary (here of week) encoutered so from friday jul 18 to friday jul 25 the result should be 1. To count the first date when it's a friday you must subtract 1 second.

I hope this can help you.

Yves

Anupama Valued Member Points: 64 More actions · Answer 6

quote:
Calculate the number of days between them and divide that number by 7.
Next, you will have to do some additional math to check if your interval starts / ends on a friday.

SELECT (DATEDIFF(d, <startdate>, <enddate>)/7)
+ CASE WHEN DATEPART(dw, <startdate>) = 6
OR DATEPART(dw, <enddate>) = 6
THEN 1
ELSE 0
END
Edited by - NPeeters on 07/23/2003 03:15:10 AM

Thanks so much, it really helped me

Anu :0

Anupama Valued Member Points: 64 More actions · Answer 7

quote:
Calculate the number of days between them and divide that number by 7.
Next, you will have to do some additional math to check if your interval starts / ends on a friday.

SELECT (DATEDIFF(d, <startdate>, <enddate>)/7)
+ CASE WHEN DATEPART(dw, <startdate>) = 6
OR DATEPART(dw, <enddate>) = 6
THEN 1
ELSE 0
END
Edited by - NPeeters on 07/23/2003 03:15:10 AM

Thanks very much 🙂

Anu