Determine Number of Fridays in Month

• dean gross

  SSCrazy

  Points: 2632

  More actions

  March 17, 2004 at 9:23 am

  #110656

  How can I determine the number of times a certain day occurs in a month? i.e, there are 4 Fridays in March and 5 in April.

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 17, 2004 at 9:56 am

  #499160

  I had a deja vu:

  I posted something here. Check it out.

   BTW I still believe a Table is the way to go

  
  * Noel

• dean gross

  SSCrazy

  Points: 2632

  More actions

  March 17, 2004 at 11:14 am

  #499172

  Thanks, but I just found the following in another newsgroup:

  /****** Object:  User Defined Function dbo.DaysInMonth    Script Date: 03/17/04 11:47:31 AM ******/

  CREATE   FUNCTION DaysInMonth

  (@day INT, @yr INT, @mon INT)

  RETURNS INT

  AS

  -- Return number of days of a given day of the week for a specified

  -- year and month.  The @day value is 1 for Sunday, 2 for Monday, etc

  BEGIN

  DECLARE @start_date DATETIME,

    @end_date DATETIME,

          @days INT,

    @wd INT

  SET @start_date = CAST(@yr AS CHAR(4)) + RIGHT('0' + CAST(@mon AS VARCHAR(2)), 2) + '01'

  SET @end_date = DATEADD(MONTH, 1, @start_date)

  SET @days = DATEDIFF(DAY, @start_date, @end_date)

  SET @wd = DATEPART(WEEKDAY, @start_date)

  RETURN(SELECT (@days - ((@day - @wd + 7) % 7) - 1) / 7 + 1)

  END

  GO