Geek Answers Handbook

List all working dates between two dates in SQL

I have SQL code to create a list of dates between two dates, but I want to generate days of the week (working days) from data of 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 please suggest me get a list of weekdays alone. There is a code in online sources to find the number of days not included in the list of all dates, there is my confusion.

+4
source share
4 answers

Try the following:

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
+5
source

Make the original query as sub-select, which generates all dates between two given dates, and then filters 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')
+4
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') ;
+3
source

Once upon a time, I built a calendar table to answer questions like yours. The main advantage, in addition to ease of use, is that you can look at the query on the calendar table and say: "This is obviously correct."

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 an idea about weekdays, so I could request it.

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

More articles:


All Articles