How to Calculate the Number of "Tuesdays" Between Two Dates in Tsql

Count work days between two dates

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

If you want to include holidays, you have to work it out a bit...

Get number of weekdays (Sundays, Mondays, Tuesdays) between two dates SQL

Given what I think you're trying to get, this should do it:

SET DATEFIRST 1

DECLARE
    @start_date DATETIME,
    @end_date DATETIME

SET @start_date = '2011-07-11'
SET @end_date = '2011-07-22'

;WITH Days_Of_The_Week AS (
    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL
    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL
    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL
    SELECT 7 AS day_number, 'Sunday' AS day_name
)
SELECT
    day_name,
    1 + DATEDIFF(wk, @start_date, @end_date) -
        CASE WHEN DATEPART(weekday, @start_date) > day_number THEN 1 ELSE 0 END -
        CASE WHEN DATEPART(weekday, @end_date)   < day_number THEN 1 ELSE 0 END
FROM
    Days_Of_The_Week

Calculate all Sundays,Mondays...Saturdays between two days in SQL Server

I think your query delivers the correct result but could be simplified a bit.

It is however dependent on SET DATEFIRST setting.

datepart(dw,[Date]) = 1 will count the number of Mondays if SET DATEFIRST is 1.

Try this:

set datefirst 7 -- Sunday
select datepart(dw, '20111227')
set datefirst 1 -- Monday
select datepart(dw, '20111227')

Result:

-----------
3

-----------
2

Update:
Another query that does the same.

select count(*) as Daycount
from master..spt_values as Number
where Number.type = 'P' and
      dateadd(day, Number.number, @StartDate) <= @EndDate and
      datepart(dw, dateadd(day, Number.number, @StartDate)) = 1

Count each days of week between two dates without loop

The date range is 30 days, dividing by 7 gives quotient 4 and remainder 2.

So every day of the week gets 4 and two days need an additional one. These are the ones corresponding to @start_date and the following day in this case.

SQL to implement this approach is below (demo)

SELECT DATENAME(WEEKDAY,base_date),
       quotient + IIF(Nums.N < remainder, 1, 0)
FROM (VALUES 
         (0),
         (1),
         (2),
         (3),
         (4),
         (5),
         (6)) Nums(N)
CROSS APPLY(SELECT 1 + DATEDIFF(DAY,@start_date,@end_date)) DC(day_count)
CROSS APPLY(SELECT DATEADD(DAY, Nums.N, @start_date), day_count/7, day_count% 7) D(base_date, quotient, remainder)
ORDER BY DATEPART(DW,base_date)

Find Mondays between 2 dates

This procedure is independent from regions and languages.

Please note the first line with SET DATEFIRST 1.

SET DATEFIRST 1; -- First day of the week is set to monday

DECLARE @DateFrom DateTime ='20150601', @DateTo DateTime = '20150630' ;

WITH CTE(dt)
AS
(
      SELECT @DateFrom
      UNION ALL
      SELECT DATEADD(d, 1, dt) FROM CTE
      WHERE dt < @DateTo
)
SELECT dt FROM CTE  where datepart ("dw", dt) = 1;

Get number of days between two dates in SQL

Use a combination of datediff and lead like this

datediff(Date_added,lead(Date_added,1))

Calculate number of working days between two dates

to get the 1 working day before @ReportDate

DATEADD(D,-1,@ReportDate)

It will be

SELECT MAX(CalDate) 
FROM   CALENDAR
WHERE  CalDate       < @ReportDate 
AND    IsHoliday     = 0  -- Not a holiday
AND    IsWorkingDay  = 1  -- is a working day

Incorporate into your query

DECLARE @ReportDate DATE = '2022-06-20' -- a Monday

SELECT *
FROM   SampleTable
WHERE  CreatedDate BETWEEN (SELECT MAX(CalDate) 
                            FROM   CALENDAR
                            WHERE  CalDate       < @ReportDate 
                            AND    IsHoliday     = 0
                            AND    IsWorkingDay  = 1)
                       AND GETDATE()

---

Related Topics