No. of sundays betn 2 dates

• Mak Mohan

  SSC Eights!

  Points: 994

  More actions

  September 19, 2008 at 1:08 am

  #193039

  Hi

  I want to calculate no. of sundays between 2 dates.

  How to do it?

  Thanks in advance.

• Alasdair Thomson

  Hall of Fame

  Points: 3630

  More actions

  September 19, 2008 at 4:37 am

  #874109

  Here is a code for counting weekdays, you can tinker to get what you want...

  CREATE FUNCTION dbo.GetWorkingDays

  (

  @startDate SMALLDATETIME,

  @endDate SMALLDATETIME

  )

  RETURNS INT

  AS

  BEGIN

  DECLARE @range INT;

  SET @range = DATEDIFF(DAY, @startDate, @endDate)+1;

  RETURN

  (

  SELECT

  @range / 7 * 5 + @range % 7 -

  (

  SELECT COUNT(*)

  FROM

  (

  SELECT 1 AS d

  UNION ALL SELECT 2

  UNION ALL SELECT 3

  UNION ALL SELECT 4

  UNION ALL SELECT 5

  UNION ALL SELECT 6

  UNION ALL SELECT 7

  ) weekdays

  WHERE d <= @range % 7

  AND DATENAME(WEEKDAY, @endDate - d + 1)

  IN

  (

  'Saturday',

  'Sunday'

  )

  )

  );

  END

  GO

  -- normal case... both working days, span a weekend

  PRINT dbo.getWorkingDays('20051109', '20051114')

  -- other... both days in same week

  PRINT dbo.getWorkingDays('20051102', '20051104')

  -- other... both days = same day

  PRINT dbo.getWorkingDays('20051104', '20051104')

  -- both days = same day, weekend

  PRINT dbo.getWorkingDays('20051105', '20051105')

  -- whole month

  PRINT dbo.getWorkingDays('20051101', '20051130')

  -- start sat, end sun

  PRINT dbo.getWorkingDays('20051112', '20051127')

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 19, 2008 at 5:00 am

  #874112

  Or use a Tally table:

  [font="Courier New"]SET DATEFORMAT YMD

  SET DATEFIRST 1 -- monday, so sunday is 7

  DECLARE @StartDate DATETIME, @EndDate DATETIME

  SET @StartDate = '2008-09-01' -- implicit conversion

  SET @EndDate = '2008-09-22' -- implicit conversion

  SELECT COUNT(*) -- The number of sundays

  FROM (

     SELECT CAST(number AS DATETIME) AS TheDate -- all sunday dates within our date range

     FROM Numbers

     WHERE number BETWEEN CAST(@StartDate AS INT) AND CAST(@EndDate AS INT) -- our date range

         AND DATEPART(DW, CAST(number AS DATETIME)) = 7 -- sundays only

  ) d

  [/font]

  Cheers

  ChrisM

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden