7

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.

user1799596
  • 117
  • 1
  • 8
  • a good way to find workdays has already been suggested [here](http://stackoverflow.com/questions/252519/count-work-days-between-two-dates) – Sim1 Mar 11 '15 at 11:15
  • Subtract two days for every week? – jarlh Mar 11 '15 at 11:16

4 Answers4

9

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
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • 1
    use of modulo 7 in `DATEDIFF(d,1 - N, @MinDate) % 7 NOT IN (5,6)` is reliable regardless of sql server settings and isn't language dependent either. – Paul Maxwell Mar 11 '15 at 11:58
  • @Used_By_Already: It's not the modulo 7 that makes this query independent of the settings but the implicit conversion of `int` to `datetime`. The problem, however, is that this solution has a bug. If you try changing `@MinDate` to `20140102`, you will get incorrect results. Perhaps t-clausen.dk meant this version of DATEDIFF: `DATEDIFF(DAY, 0, DATEADD(DAY, N - 1, @MinDate)) % 7 NOT IN (5,6)`. – Andriy M Mar 13 '15 at 10:13
  • I was not referring to the cause of settings independence but the "use of modulo 7". I was attempting to describe what the "% 7" did and offered as a benefit (perhaps poorly) which is that the result of (5,6) is settings independent. You may indeed be correct regarding the need for that dateadd(), I'll let the author respond to that. Cheers. – Paul Maxwell Mar 13 '15 at 12:18
6

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')
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • immaculate except for some reason my SQL explorer won't let a table be a table. – CashCow Dec 18 '17 at 16:22
  • Set DateFirst has no effect on the DateDiff, DateAdd or DateName functions in your code. That line can be removed with no effect. Also you need to give the implied table in the () an alias or it won't run in SQL Server (e.g. ... Cross Join sys.all_objects b) MyTemp where datename(.... – Jeff Winchell Apr 16 '20 at 14:44
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') ;
Matt
  • 14,906
  • 27
  • 99
  • 149
  • Thanks for your reply Matt, but weekends (Saturday and Sunday) are not getting filtered. I dont want the weekend dates in my code. – user1799596 Mar 11 '15 at 11:32
  • When comparing to check for saturday and sunday datepart is not reliable, it depends of the setting of datefirst which default can be different from database to database – t-clausen.dk Mar 11 '15 at 11:35
  • Yeh i thought it may be, ill remove the second version – Matt Mar 11 '15 at 11:41
  • 1
    still im not getting the correct output, for eg, if i am passing these value as start date and end date '2015/12/13','2015/12/27', no dates are getting generated. – user1799596 Mar 11 '15 at 11:45
  • Because your start date and end date there are weekend dates, if your using them they wont work as there not included, try '2015/12/14' & '2015/12/25' – Matt Mar 11 '15 at 11:53
2

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;
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185