By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Calculating the Weekend Days(Saturaday,Sunday)

Manikgisl
P: 37
HI,

I have two different dates

26-12-2008 to 26-01-2009
these are the two dates ..

i want calculate number of saturday and sundays in between two date


anybody having an idea....
Pls help me

With Regards,

Mani
Mar 12 '09 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
What's the biggest (in days) the possible range of your FromDate and ToDate?


-- CK
Mar 12 '09 #2

P: 30
You can do a count() or have a countter for each type of day for the number of entries that return Saturday and Sunday using DATENAME() function.
Mar 12 '09 #3

Manikgisl
P: 37
hi
I Having the 26 27 28 29 30 as Fields in our tables ..
i want marked 'O' in the Saturday and Sunday Coloumn\


Thanks In advance

With regards
Mani
Mar 13 '09 #4

Delerna
Expert 100+
P: 1,134
@Manikgisl

Hi Mani
This is purely a matematics exercise
Here is one method (I think it is right) :-}

Expand|Select|Wrap|Line Numbers
  1. declare @Start datetime,@End Datetime
  2. set @Start='2009-03-14'
  3. set @End='2009-03-29'
  4.  
  5.  
  6. select WkDay,
  7.        TotDays,
  8.        DaysTillNextSat,
  9.        DaysTillNextSun,
  10.        (7-DaysTillNextSat)/7+(TotDays-DaysTillNextSat)/7+case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end as NumSats,
  11.        (8-DaysTillNextSun)/7+(TotDays-DaysTillNextSun)/7 as NumSuns
  12. from
  13. (   select WkDay,TotDays,
  14.               7-WkDay as DaysTillNextSat,case when 7-WkDay+1=7 then 0 else 7-WkDay+1 end as DaysTillNextSun
  15.     from(   select datepart(dw,@Start) as WkDay,
  16.                        datediff(dd,@Start,@End)as TotDays
  17.         )a
  18. )a
  19.  
I have tried to write the query so you can follow my logic (hopefully)
rather than giving you a concice query

Take the query, analyse it and reduce it so that it returns only the fields you need.
When done, make a user defined function out of it.

I hope it helps you

If anyone can come up with a mathematically neater way then I for one would like to see it

Regards
Mar 18 '09 #5

Delerna
Expert 100+
P: 1,134
I thought I might try and explain this line a little bit
Expand|Select|Wrap|Line Numbers
  1. (7-DaysTillNextSat)/7+(TotDays-DaysTillNextSat)/7+case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end as NumSats
  2.  
TotDays is the number of days in the date range

(7-DaysTillNextSat)/7 will be 1 if @StartDate is a saturday otherwise 0

This bit
(TotDays-DaysTillNextSat)/7
gives the number of saturdays (one per week)
over and above @StartDate being a saturday

However, if
Totdays-DaysTillNextSat
is less than 7 then it will be 0
but there still might be a saturday somwere in there

so this bit
case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end
corrects the result in that case

The DaysTillNextSat<>0 criteria is there because
@Startdate being saturday has already been covered
Mar 18 '09 #6

P: 1
this function is close but seems not to work 100% ( at least on my end)
Please try these DateTime Stamps

2009-04-03 11:42:36 (yyyy-mm-dd)
2009-04-06 09:16:49

It should come up with 1 Sat and 1 Sun, but for me it only return 1 Sat and 0 Sun.
Apr 14 '09 #7

ck9663
Expert 2.5K+
P: 2,878
Let me try.......

This query will return all the days from your start date to end date....

Expand|Select|Wrap|Line Numbers
  1. declare @startdate as datetime, @enddate as datetime
  2.  
  3. select  @startdate = '12-26-2008', @enddate = '01-26-2009'
  4.  
  5. select    
  6.    dateadd(dd,days,@startdate), datename(dw,dateadd(dd,days,@startdate))
  7. from
  8. (select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
  9. where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0
  10.  
Here's the code that will count all weekends..

Expand|Select|Wrap|Line Numbers
  1.  
  2. select
  3.    sum(       
  4.    case 
  5.       when datename(dw,dateadd(dd,days,@startdate)) in ('SATURDAY', 'SUNDAY') then 1
  6.       else 0
  7.    end)
  8. from
  9. (select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
  10. where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0
  11.  
  12.  
so is this...

Expand|Select|Wrap|Line Numbers
  1.  
  2. select
  3.    count(*)
  4. from
  5. (select top 365 colorder - 1 as days from master..syscolumns where id = -519536829 order by colorder) x
  6. where datediff(dd,dateadd(dd,days,@startdate),@enddate) >= 0 and datename(dw,dateadd(dd,days,@startdate)) in ('SATURDAY', 'SUNDAY')
  7.  
  8.  
All queries will run if your dates are one year apart. If you need more, adjust the TOP 365 portion.


--- CK
Apr 15 '09 #8

Delerna
Expert 100+
P: 1,134
Yea, you're right
In my explanation of the query I said
However, if
Totdays-DaysTillNextSat
is less than 7 then it will be 0
but there still might be a saturday somewere in there

so this bit
case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end
corrects the result in that case

The DaysTillNextSat<>0 criteria is there because
@Startdate being saturday has already been covered
I didn't do the same thing for sunday
so in the above explanation substitute sunday for saturday
and add the code being described to the NumSuns calculation

specifically, this bit

+case when DaysTillNextSun<=totdays and DaysTillNextSun<>0 then 1 else 0 end


Expand|Select|Wrap|Line Numbers
  1. declare @Start datetime,@End Datetime 
  2. set @Start='2009-04-3 11:42:36' 
  3. set @End='2009-04-06 09:16:49' 
  4.  
  5.  
  6. select WkDay,
  7.        TotDays, 
  8.        DaysTillNextSat, 
  9.        DaysTillNextSun, 
  10.        (7-DaysTillNextSat)/7+(TotDays-DaysTillNextSat)/7+case when DaysTillNextSat<=totdays and DaysTillNextSat<>0 then 1 else 0 end as NumSats, 
  11.        (8-DaysTillNextSun)/7+(TotDays-DaysTillNextSun)/7+case when DaysTillNextSun<=totdays and DaysTillNextSun<>0 then 1 else 0 end as NumSuns 
  12. from 
  13. (
  14.    select WkDay,TotDays, 
  15.               7-WkDay as DaysTillNextSat,case when 7-WkDay+1=7 then 0 else 7-WkDay+1 end as DaysTillNextSun 
  16.     from(   select datepart(dw,@Start) as WkDay, 
  17.                        datediff(dd,@Start,@End)as TotDays 
  18.         )a 
  19. )a 
  20.  
Apr 21 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.