How to generate all dates between two dates

Question

How can I retrieve all dates between '2015-10-02' to '2015-11-02' in SQLite? (String type) Result will be like:

'2015-10-03'
'2015-10-04'
'2015-10-05'
...
'2015-11-01'

This is not a question about SELECT * FROM myTable where myDate <= '2015-01-01' AND myDate >= '2015-01-31'. This is not about selecting all existing records which have a field between two days. I just want to retrieve all possible date values between two dates. I want to use them to query the count of record by days.

Date             Count
'2015-01-01'      19
'2015-01-02'      10
'2015-01-03'      0
...

Possible duplicate of [SQLite compare dates](http://stackoverflow.com/questions/13091000/sqlite-compare-dates) — David Ferenczy Rogožan, Oct 07 '15 at 01:39
http://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par looks relevant, but I'm not up on possible dialect differences between SQL Server and SQLite. — Nathan Tuggy, Oct 07 '15 at 03:18

score 40 · Accepted Answer · answered Oct 07 '15 at 08:14

This is not possible without a recursive common table expression, which was introduced in SQLite 3.8.3:

WITH RECURSIVE dates(date) AS (
  VALUES('2015-10-03')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < '2015-11-01'
)
SELECT date FROM dates;

score 0 · Answer 2 · answered Oct 08 '21 at 14:35

If you need it regularly, it may be worth generating a table holding all the dates in the range that you need.

Using Bash in Linux and it's date command, it's easy to generate the dates and populate a table with them.

Assuming you have a "days" table with a "d" column:

d='2019-01-01'          # start date
end='2021-12-31'        # end date
while ! [[ $d > $end ]]; do
  echo $d
  d=$(date -d "$d + 1 day" +%F)
done \
| sqlite3 $Your_DB '.import /dev/stdin days'

Or as a (long) one-liner:

d='2019-01-01'; end='2021-12-31'; while ! [[ $d > $end ]]; do echo $d; d=$(date -d "$d + 1 day" +%F); done | sqlite3 $Your_DB '.import /dev/stdin days'

How to generate all dates between two dates

2 Answers2

Linked