List all working dates between two dates in SQL

Question

i have the SQL code to generate the date list between two dates, but i want to generate the week days (Business days) from the given two dates,

DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106';
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

This is my code, so anyone please suggest me to get the weekdays list alone. Online sources have code to find the number of days not for to list all dates, there lies my confusion.

Answer 1

Try this:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106'

;WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT  Date = DATEADD(DAY, N - 1, @MinDate)
FROM    N4
WHERE 
  N < DATEDIFF(DAY, @MinDate, @MaxDate) + 2 AND
  DATEDIFF(DAY, 1 - N, @MinDate) % 7 NOT IN (5,6)

Result:

Date
2014-01-01
2014-01-02
2014-01-03
2014-01-06

Answer 2

Make the original query as sub-select which generates all the dates between two given dates then do the filteration in outer query.

SET DATEFIRST 1

select [Date] from
(
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM    sys.all_objects a
    CROSS JOIN sys.all_objects b
)
where datename(dw,[Date]) not in ('Saturday','Sunday')

Answer 3

DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106';
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
WHERE datename(dw,@MinDate) NOT IN ('Saturday','Sunday') 
      AND datename(dw,@MaxDate) NOT IN ('Saturday','Sunday') ;

Answer 4

A long time ago, I built a calendar table to answer questions like yours. The main benefit, besides ease of use, is that you can look at a query against the calendar table and say, "That's obviously right."

select cal_date, day_of_week
from calendar
where day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
  and cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;

cal_date    day_of_week
--
2014-01-01  Wed
2014-01-02  Thu
2014-01-03  Fri
2014-01-06  Mon

I also have a view of weekdays, so I could have queried it instead.

select cal_date, day_of_week
from weekdays
where cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;