Display List of Dates from Start and End Date in SQL

Question

I need display a list of dates in SQL. Already I have an input with single row for n number codes.

Present Table Structure (dates in day/month/year format):

Code FromDate   ToDate     Total Days

1    01/03/2012 04/03/2012   4
2    01/04/2012 05/04/2012   5

I need output as:

Code FromDate

1    01/03/2012
1    02/03/2012
1    03/03/2012
1    04/03/2012

Use a loop or a dates lookup table, or just do it in the front end. What did you try that didn't work? — lc., Nov 01 '12 at 11:18
Have you tried anything yourself? What DB engine are you using? Isn't `total days` redundant? — juergen d, Nov 01 '12 at 11:18
Generating a list of dates from a start date and an end date in pure SQL is not completely trivial. It's more easily done in a procedural language. I don't think the down votes are entirely fair. And I'd be more convinced by close votes if they were for a duplicate question — the difficulty, as ever, is finding the duplicate. It would help if the DBMS were specified. — Jonathan Leffler, Nov 01 '12 at 11:29
Whoops, see this answer: http://stackoverflow.com/questions/13153624/php-function-to-loop-business-days-between-two-posted-dates/13154005#13154005 — Salman A, Nov 01 '12 at 11:31
That (SO 13153624) is a PHP solution; it's good if PHP is an option, but the question is only labelled SQL. — Jonathan Leffler, Nov 01 '12 at 11:32

score 2 · Answer 1 · answered Nov 01 '12 at 11:22

WITH date_range (calc_date) AS (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, '2010-01-13') - DATEDIFF(DAY, '2010-01-01', '2010-01-13'), 0)
        UNION ALL SELECT DATEADD(DAY, 1, calc_date)
            FROM date_range
            WHERE DATEADD(DAY, 1, calc_date) <= '2010-01-13')
SELECT calc_date
FROM date_range;

score 1 · Answer 2 · edited May 23 '17 at 12:03

Although you can write code that extrapolates the dates (Such as VikramJain's recursive CTE for SQL Server) it's CPU heavy. If you have large numbers of records, over large ranges of time, you're iteratively building large amounts of data. And you're doing it every time you query your data. By far the most CPU efficient method is simply to have another table.

If you think of your database in terms of Dimension tables and Fact tables, your start and end dates are just keys to an implied Dimension of Time. Instead of being implicit, be explicit, and create a Calendar table. Then it's trivial...

SELECT
  yourTable.code,
  calendar.calendar_date
FROM
  yourTable
INNER JOIN
  calendar
    ON  calendar.calendar_date >= yourTable.fromDate
    AND calendar.calendar_date <  yourTable.toDate

This potentially saves huge amounts of CPU load, and massively simplifies your queries.

Once you have the table, pre-populated with dates covering everything you will ever need, many messy date manipulations become simple indexable lookups.

You can even add to that table meta-data such as...

start_of_week
start_of_month
financial_year
etc, etc

Some people criticise this as inelegant. Personally I feel the opposite:
- It caches messy date based calculations
- It explicity creates a dimension table

It's even used by TeraData to very good effect: sys_calendar.calendar.

Display List of Dates from Start and End Date in SQL

2 Answers2