Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Number of Mondays between two dates Expand / Collapse
Author
Message
Posted Thursday, February 16, 2006 4:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 24, 2012 2:01 PM
Points: 31, Visits: 104
Hey, I'm looking for t-sql that can take two dates and determine how many mondays are between them?


Post #258968
Posted Thursday, February 16, 2006 4:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

I think this will give the number of Mondays between the two dates (both inclusive). 20060213 can be replaced by any Monday...

select (datediff(d, '20060213', @Date2) + 1)/7 - datediff(d, '20060213', @Date1)/7

Post #258979
Posted Thursday, February 16, 2006 5:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 24, 2012 2:01 PM
Points: 31, Visits: 104
This is an interesting start, but not quite what I'm looking for.  In this solution you have to know the first Monday for any calculation.  I'm looking to take to dates and then calculate the number of mondays between them without having to know the first Monday in T-SQL, if possible.


Post #258987
Posted Thursday, February 16, 2006 5:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

I don't quite understand what you're saying. As an example, to calculate the number of Mondays in the year 2000, do as follows:

select (datediff(d, '20060213', '20001231') + 1)/7 - datediff(d, '20060213', '20000101')/7

Could you give an example where my method returns something wrong?

 

Post #258990
Posted Thursday, February 16, 2006 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 24, 2012 2:01 PM
Points: 31, Visits: 104

Hey, I appreciate your feedback.  I'm not saying that your solution is wrong.  What I'm looking for is a formula that would be inside a stored procedure.  Another stored procedure will be calling it and all this stored procedure would know would be the start date and the end date.  From that, I'd like to calculate the number of Mondays between the two dates.

Taffy




Post #258999
Posted Thursday, February 16, 2006 5:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

Something like this?

 

create proc countMondays
(
  @FromDate datetime,
  @ToDate datetime,
  @NumberOfMondays int output
)
as
  select @NumberOfMondays = (datediff(d, '20060213', @ToDate) + 1)/7 - datediff(d, '20060213', @FromDate)/7
go

declare @count int
exec countMondays '20000101', '20001231', @count output
select @count

Post #259002
Posted Thursday, February 16, 2006 6:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 19, 2009 4:02 PM
Points: 223, Visits: 108

For this i have created a function in which you have to pass two date from to to date

and it will calculate the no.of  mondays in the given two dates

 

 

Create Function Monday(@dt1 datetime,@dt2 datetime) returns int
as
Begin
Declare @cnt int,@dt as datetime

set @cnt=0
if @dt1 < @dt2
Begin
 set @dt=@dt1
 while @dt <= @dt2
 Begin
  if Datepart(dw,@dt)=1
  Begin
   set @cnt=@cnt+1
   set @dt=dateadd(dd,1,@dt)
  End
  set @dt=dateadd(dd,1,@dt)
 End
End

if @cnt=0
Begin
  return 0
End
Return @cnt
End


select dbo.Monday('2005-01-01','2005-02-28')

 

ok bye??

 

 

Post #259011
Posted Thursday, February 16, 2006 6:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, June 17, 2011 6:28 AM
Points: 422, Visits: 33

Note that the output of your function depends on the value of @@DATEFIRST:

declare @DateFirst int
select @DateFirst = @@DATEFIRST

set datefirst 1
select dbo.Monday('2006-02-13','2006-02-20')  --Returns 2
set datefirst 7
select dbo.Monday('2006-02-13','2006-02-20')  -- Returns 1 (incorrect)

set datefirst @DateFirst -- Resets @@DATEFIRST to original value


 

Post #259017
Posted Thursday, February 16, 2006 10:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:34 AM
Points: 255, Visits: 74

This could be wrapped in either a function or stored procedure.  As noted above this also depends on the value of @@DATEFIRST.

--using a standard seq table
 SELECT TOP 9999
        IDENTITY(INT,0,1) AS N
 INTO  #Seq
   FROM Master.dbo.SysComments sc1,
        Master.dbo.SysComments sc2

declare @startdate datetime
declare @enddate datetime

select @startdate = '13 Feb 2006',
       @enddate = '20 Feb 2006'

select sum(case when datepart(dw, dateadd(dd, s.n, @startdate)) = 1 then 1 else 0 end)
from #seq s
where s.n <= datediff(dd, @startdate, @enddate)

drop table #SEQ

 

Post #259126
Posted Thursday, February 16, 2006 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 28, 2008 5:46 PM
Points: 61, Visits: 371

Amit,

Nice function - I think one small change could also speed it up - since weeks are a predictable format, when you encounter a Monday skip ahead the next 6 days - the second dateadd will then add one more day - once you find a Monday, you only need to check the Mondays....

Create Function Monday(@dt1 datetime,@dt2 datetime) returns int
as
Begin
Declare @cnt int,@dt as datetime

set @cnt=0
if @dt1 < @dt2
Begin
 set @dt=@dt1
 while @dt <= @dt2
 Begin
  if Datepart(dw,@dt)=1
  Begin
   set @cnt=@cnt+1
   set @dt=dateadd(dd,6,@dt)
  End
  set @dt=dateadd(dd,1,@dt)
 End
End

if @cnt=0
Begin
  return 0
End
Return @cnt
End

select dbo.Monday('2005-01-01','2005-02-28')

 

Regards,

Harley

Post #259173
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse