-1

So I have an empty table in a database in SQLite, just with one column (DATES).

Any idea how to automatically update this column with dates from 2019/01/01 to 2019/12/31?

I would like to know if there is any specific code in SQL for such a simple thing that in spreadsheet would be just drag down.

Thank you so much!

Val10
  • 111
  • 1
  • 1
  • 4
  • I put this up a few days ago as an answer to another similar question: https://stackoverflow.com/a/53054594/9952196 – Shawn Nov 02 '18 at 22:38
  • Thanks I just saw your answer. I was just wondering if there is a more straight way to do it, since it seems quite few lines of code for such a standard and simple thing – Val10 Nov 02 '18 at 22:46
  • 1
    It's only a few lines, and not something I've seen done very often. Writing SQL is just like programming... You have to build what you need out of smaller parts, there's not a magic "do what I want" wand that solves all problems with a single function. – Shawn Nov 03 '18 at 00:13
  • Look at [my answer here](https://stackoverflow.com/a/69497590/111036). It has a command to populate a "days" table in your wanted range, which you can then use easily in your queries. – mivk Oct 08 '21 at 14:41

1 Answers1

0

I don't recommend updating the existing table. Just create a new one:

create table dates as
with cte as (
      select date('2019-01-01') as dte union all
      select date(dte, '+1 day')
      from cte
      where dte < '2020-12-31'
     )
select *
from cte;

If this is for occasional use, you can just use the CTE rather than storing the results in a table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786