Search code examples
sqlsql-serversqlitedaterecursive-query

Select Table of Days For One Year SQLITE

I am trying to select a table of every date for the last year.

In SQL Server, I can run something like this:

SELECT TOP (DATEDIFF(DAY, DATEADD(YEAR, - 1, GETDATE()), GETDATE()) + 1) 
    Date = CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, DATEADD(YEAR, - 1, GETDATE())) AS DATE) 
FROM sys.all_objects a

It returns 1 column with 366 rows containing the dates from 1 year ago until now.

I am looking for something equivalent in SQLITE.

Solution

  • You can do it with a recursive CTE:

    WITH cte AS (
  SELECT DATE(CURRENT_DATE, '-1 year') date
  UNION ALL
  SELECT DATE(date, '+1 day') 
  FROM cte
  WHERE date < CURRENT_DATE
)
SELECT * FROM cte;

    See the demo.