Get all dates for all date ranges in table using SQL Server

sql generate date range
sql server generate dates between two dates
sql create multiple rows from date range
sql create table of dates
sql generate list of dates
sql create row for each date
sql calendar table
udf-range-date

I have table dbo.WorkSchedules(Id, From, To) where I store date ranges for work schedules. I want to create a view that will have all dates for all rows of WorkSchedules. Thanks to this I have 1 view with all dates for all schedules.

On web I only found solutions for 1 row like 2 parameters start and end. My issue is different where I have multiple rows with start and end range.

Example:

WorkSchedules

Id | From       | To
---+------------+-----------
1  | 2018-01-01 | 2018-01-05 
2  | 2018-01-08 | 2018-01-12

Desired result

1 | 2018-01-01
2 | 2018-01-02
3 | 2018-01-03
4 | 2018-01-04
5 | 2018-01-05
6 | 2018-01-08
7 | 2018-01-09
8 | 2018-01-10
9 | 2018-01-11
10| 2018-01-12

If you are regularly dealing with "jobs" and "schedules" then I propose that you need a permanent calendar table (a table where each row is a unique date). You can create rows for dates dynamically but why do this many times when you can do it once and just re-use?

A calendar table, even of several decades, isn't "big" and when indexed they can be very fast as well. You can also store information about holidays and/or fiscal periods etc.

There are many scripts available to produce these tables, here's an answer with 2 scripts on this site: https://stackoverflow.com/a/5635628/2067753

Assuming you use the second (more comprehensive) script, then you can exclude weekends, or other conditions such as holidays, from query results.

Once you have a permanent Calendar table this style of query may be used:

CREATE TABLE WorkSchedules(
   Id   INTEGER  NOT NULL PRIMARY KEY 
  ,[From] DATE  NOT NULL
  ,[To]   DATE  NOT NULL
);
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (1,'2018-01-01','2018-01-05');
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (2,'2018-01-12','2018-01-12');

with range as (
    select min(ws.[From]) as dt_from, max(ws.[To]) dt_to
    from WorkSchedules as ws
    )
select c.*
from calendar as c
inner join range on c.date between range.dt_from and range.dt_to
where c.KindOfDay = 'BANKDAY'
order by c.date

and the result looks like this (note: "News Years Day" has been excluded)

              Date           Year   Quarter   Month   Week   Day   DayOfYear   Weekday   Fiscal_Year   Fiscal_Quarter   Fiscal_Month   KindOfDay   Description  
 ---- --------------------- ------ --------- ------- ------ ----- ----------- --------- ------------- ---------------- -------------- ----------- ------------- 
   1   02.01.2018 00:00:00   2018         1       1      1     2           2         2          2018                1              1   BANKDAY     NULL         
   2   03.01.2018 00:00:00   2018         1       1      1     3           3         3          2018                1              1   BANKDAY     NULL         
   3   04.01.2018 00:00:00   2018         1       1      1     4           4         4          2018                1              1   BANKDAY     NULL         
   4   05.01.2018 00:00:00   2018         1       1      1     5           5         5          2018                1              1   BANKDAY     NULL         
   5   08.01.2018 00:00:00   2018         1       1      2     8           8         1          2018                1              1   BANKDAY     NULL         
   6   09.01.2018 00:00:00   2018         1       1      2     9           9         2          2018                1              1   BANKDAY     NULL         
   7   10.01.2018 00:00:00   2018         1       1      2    10          10         3          2018                1              1   BANKDAY     NULL         
   8   11.01.2018 00:00:00   2018         1       1      2    11          11         4          2018                1              1   BANKDAY     NULL         
   9   12.01.2018 00:00:00   2018         1       1      2    12          12         5          2018                1              1   BANKDAY     NULL         

Without the where clause the full range is:

              Date           Year   Quarter   Month   Week   Day   DayOfYear   Weekday   Fiscal_Year   Fiscal_Quarter   Fiscal_Month   KindOfDay    Description    
 ---- --------------------- ------ --------- ------- ------ ----- ----------- --------- ------------- ---------------- -------------- ----------- ---------------- 
   1   01.01.2018 00:00:00   2018         1       1      1     1           1         1          2018                1              1   HOLIDAY     New Year's Day  
   2   02.01.2018 00:00:00   2018         1       1      1     2           2         2          2018                1              1   BANKDAY     NULL            
   3   03.01.2018 00:00:00   2018         1       1      1     3           3         3          2018                1              1   BANKDAY     NULL            
   4   04.01.2018 00:00:00   2018         1       1      1     4           4         4          2018                1              1   BANKDAY     NULL            
   5   05.01.2018 00:00:00   2018         1       1      1     5           5         5          2018                1              1   BANKDAY     NULL            
   6   06.01.2018 00:00:00   2018         1       1      1     6           6         6          2018                1              1   SATURDAY    NULL            
   7   07.01.2018 00:00:00   2018         1       1      1     7           7         7          2018                1              1   SUNDAY      NULL            
   8   08.01.2018 00:00:00   2018         1       1      2     8           8         1          2018                1              1   BANKDAY     NULL            
   9   09.01.2018 00:00:00   2018         1       1      2     9           9         2          2018                1              1   BANKDAY     NULL            
  10   10.01.2018 00:00:00   2018         1       1      2    10          10         3          2018                1              1   BANKDAY     NULL            
  11   11.01.2018 00:00:00   2018         1       1      2    11          11         4          2018                1              1   BANKDAY     NULL            
  12   12.01.2018 00:00:00   2018         1       1      2    12          12         5          2018                1              1   BANKDAY     NULL            

and weekends and holidays may be excluded using the column KindOfDay

See this as a demonstration (with build of calendar table) here: http://rextester.com/CTSW63441

SQL Server Function to return a range of dates, SQL Server Function to return a range of dates I can use the function to show a list of orders by date as well as any dates that have gaps as You can find a similar function I create using a tally table, a table with one column  I could have used the DATE data type instead, but I wanted this to work for SQL Server 2005 and later versions. While this function was built with only the day, week and month increments. It might be interesting to add all the same increments as you would get with some of the built in functions.

Ok, I worked this out for you, thinking you mean that you meant 01/08/2018 as a From date in the second row.

/*WorkSchedules
Id|    From    |     To
1 | 2018-01-01 | 2018-01-05 
2 | 2018-01-08 | 2018-01-12
*/

--DROP TABLE #WorkSchedules;

CREATE TABLE #WorkSchedules (
    ID int,
    [DateFrom] DATE,
    [DateTo] DATE
    )

INSERT INTO #WorkSchedules
SELECT 1, '2018-01-01', '2018-01-05'
UNION
SELECT 2, '2018-01-08', '2018-01-12'

;WITH CTEDATELIMITS AS (
    SELECT [DateFrom], [DateTo]
    FROM #WorkSchedules
    )
,CTEDATES AS
(
SELECT [DateFrom] as [DateResult] FROM CTEDATELIMITS
UNION ALL
SELECT DATEADD(Day, 1, [DateResult]) FROM CTEDATES
JOIN CTEDATELIMITS ON CTEDATES.[DateResult] >= CTEDATELIMITS.[DateFrom]
AND CTEDATES.dateResult < CTEDATELIMITS.[DateTo]
)
SELECT [DateResult] FROM CTEDATES
ORDER BY [DateResult]

Creating a date dimension or calendar table in SQL Server, In this example, I'm going to populate the date dimension table with data a second CTE that translates those numbers into all the dates in our range: in a view or table than it is to have every query calculate them inline. Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 33 Pluralsight courses and has written over 5100 articles on the database technology on his blog at a https://blog.sqlauthority.com. Along with 17+ years of hands-on experience, he holds a Masters of Science degree

You would use a recursive CTE:

with dates as (
      select from, to, from as date
      from WorkSchedules
      union all
      select from, to, dateadd(day, 1, date)
      from dates
      where date < to
     )
select row_number() over (order by date), date
from dates;

Note that from and to are reserved words in SQL. They are lousy names for identifiers. I have not escaped them because I assume they are not the actual names of the columns.

How to create a row for every day in a date range using a stored , I am using SQL Server 2008 R2 in my test environment, but our real Another option is to use a Table-Valued-Function. You supply the Date/Time Range, DatePart and Increment. Holidays is a table that contains holiday dates. a purely SQL-based solution (no stored procedures) to getting a row for every hour in a  Using Calendar Table. One of the most convenient ways of finding missing dates in a date range is by using a Calendar table. There’s a ton of materials on this topic. Here’s one that tells you why you need a Calendar table. For our demo purpose, lets create a simple calendar table that will contain our date range.

Get all dates between range, How can get a list of all the dates between two dates (current_date and another In SQL Server I can do this using recursive SQL but looks like that My date dimension table has a column "date_id" which has dates from  You would use a recursive CTE: with dates as ( select from, to, from as date from WorkSchedules union all select from, to, dateadd(day, 1, date) from dates where date < to ) select row_number() over (order by date), date from dates; Note that from and to are reserved words in SQL. They are lousy names for identifiers.

Selecting records between two date range query, We can combine all this and try for getting records between two date ranges. Between Now let us move to select a range of records between two dates. Here is Here is the code for the SQL dump of the file to create your table for testing. In SQL Server there is no direct function or procedure that returns all the months within a date range (all days between two dates). This article provides a workaround to get the months, including the name(s), of the dates in a range of dates.

List All Dates Between Two Dates in SQL Server, In this video, we used MultiLine table valued function to get All dates between two dates Duration: 7:12 Posted: Apr 6, 2017 Many times when developing SQL Server databases you have the requirement to show all dates in a range. One way to do this is with a date table. One of the most common reasons that I use a date table is when I have a reconciliation report. In a reconciliation report I want to show all of the days in a date range, even if they don’t have data

Comments
  • Sample data and desired results would really help
  • Added example result
  • Where 08, 09, 10, 11 comes from?? and what happen to 06, 07? based on which conditions?
  • I really hope you do NOT use [From] or [To] as column names.