Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List all working dates between two dates in SQL

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.

like image 954
user1799596 Avatar asked Mar 11 '15 11:03

user1799596


People also ask

How do I get all dates between two dates in SQL?

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.

How do I count days between two dates in SQL Server?

To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.

How do I create a list of dates in SQL?

Now, in order to generate all the dates in the sale report, first we are generating a list of all dates between start and end date using recursive CTE and then using Left Join with the existing query to display all the dates in the result. Related posts: Generate Weekdays in SQL Server.

How do I select a specific date range in SQL?

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2001-03-01 11:00:00' AND '2005-03-01 22:00:00';


4 Answers

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
like image 161
t-clausen.dk Avatar answered Sep 28 '22 06:09

t-clausen.dk


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')
like image 37
Pரதீப் Avatar answered Sep 28 '22 06:09

Pரதீப்


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') ;
like image 37
Matt Avatar answered Sep 28 '22 07:09

Matt


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;
like image 34
Mike Sherrill 'Cat Recall' Avatar answered Sep 28 '22 07:09

Mike Sherrill 'Cat Recall'