Get Number of Weekdays (Sundays, Mondays, Tuesdays) Between Two Dates SQL

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 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...

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;

Count specific days of week between two dates

Method taken and adapted from here

@S          = start date
@E          = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days       = (@E-@S) - @full_weeks*7   OR (@E-@S) % 7

SELECT
  @full_weeks*1 -- saturday
 +IF( @days >= 1 AND weekday( S+0 )=5, 1, 0 )
 +IF( @days >= 2 AND weekday( S+1 )=5, 1, 0 )
 +IF( @days >= 3 AND weekday( S+2 )=5, 1, 0 )
 +IF( @days >= 4 AND weekday( S+3 )=5, 1, 0 )
 +IF( @days >= 5 AND weekday( S+4 )=5, 1, 0 )
 +IF( @days >= 6 AND weekday( S+5 )=5, 1, 0 )

Done.

Working SQL Fiddle

T-SQL - Determine number of specific days between 2 dates based on sales

If I understand correctly, you can use a calendar table to count the number of days where the day of week is n and between the start and end and is a date that has ticket sales, which I guess is when the date exists in tickets and has the sum(c_items_total) > 0

WITH cal AS 
(
  SELECT cast('2012-01-01' AS DATE) dt, datepart(weekday, '2012-01-01') dow
  UNION ALL      
  SELECT dateadd(day, 1, dt), datepart(weekday, dateadd(day, 1, dt))
  FROM cal
  WHERE dt < getdate()
)

SELECT COUNT(1)
FROM cal
WHERE dow = 5 
  AND dt BETWEEN '2012-04-01' AND '2012-12-31'
  AND EXISTS (
    SELECT 1
    FROM tickets
    WHERE cast(dt_create_time AS DATE) = dt
    GROUP BY cast(dt_create_time AS DATE)
    HAVING sum(c_items_total) > 0
    )
OPTION (MAXRECURSION 0)

SQLFiddle

MySQL function to find the number of working days between two dates

This expression -

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

calculates the number of business days between the start date @S and the end date @E.

Assumes end date (@E) is not before start date (@S).
Compatible with DATEDIFF in that the same start date and end date
gives zero business days.
Ignores holidays.

The string of digits is constructed as follows. Create a table of
start days and end days, the rows must start with monday (WEEKDAY
0) and the columns must start with Monday as well. Fill in the
diagonal from top left to bottom right with all 0 (i.e. there are 0
working days between Monday and Monday, Tuesday and Tuesday, etc.).
For each day start at the diagonal (must always be 0) and fill in
the columns to the right, one day at a time. If you land on a
weekend day (non business day) column, the number of business days
doesn't change, it is carried from the left. Otherwise, the number
of business days increases by one. When you reach the end of the
row loop back to the start of the same row and continue until you
reach the diagonal again. Then go on to the next row.

E.g. Assuming Saturday and Sunday are not business days -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

Then concatenate the 49 values in the table into the string.

Please let me know if you find any bugs.

-Edit
improved table:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

---

Related Topics