-2

"How can i create in sqlite a Table with 365 Rows and how can i insert the dates?"

For example: Table MyTable

id  month   date
1   jan 2021-01-01
2   jan 2021-01-02
3   jan 2021-01-03
.
.
.
365 dec 2021-12-31

How can i create this automatically ? Thanks

emrepb
  • 123
  • 7

1 Answers1

1

You may reach this using recursive query

create table days as
with recursive qq as (
  select 1 id, 'jan' month, '2021-01-01' date_col union all
  select id + 1, substr('janfebmaraprmayjunjulaugsepoctnovdec', 1 + 3 * 
  strftime('%m', date(date_col, '+1 day')), -3), date(date_col, '+1 day')
   from qq
  where id <= 364
)

The monstrous line

substr('janfebmaraprmayjunjulaugsepoctnovdec', 1 + 3 * strftime('%m', date(date_col, '+1 day')), -3)

will cut the name of the month you need from the line based on the month number of the date. Ths one is needed because SQLIte seems to be not able to get month name from date.

There were suggestions of how one can get month names from date but it did not worked for me. Try those if you want

Here's an example on dbfiddle

ekochergin
  • 4,109
  • 2
  • 12
  • 19
  • Thank you very much. it works fine :) I have another question. Is it possible to distribute the days in the month in calendar weeks ? or get the calendar weeks for the given month ? Example January: CW1: 04.01. 05.01 06.01 07.01 08.01 09.01 10.01 – emrepb Dec 14 '21 at 13:21
  • @emrepb sorry, not sure I am following you. Can you update the question with an example of desired output please? – ekochergin Dec 14 '21 at 13:47
  • @emrepb %W in `strftime()` will give the week of the year. See https://www.sqlite.org/lang_datefunc.html – Shawn Dec 14 '21 at 19:18