dimanche 21 février 2016

third Wednesdays in sqlite (sequences and date())

I want to calculate the dates of all third Wednesdays this year. The date() command has a nice facility for time increments, so we get third Wednesday in Feb 2016 by calculating first one, and adding two weeks:

select date("2016-02-01","weekday 3","14 days")

Now, we need to iterate over all 12 months. I use a recursive trick I found

with rec as 
  (select 0 i union select i+1 from rec where i<11) 
select 
  date("now","start of year", i||" month", "weekday 3","14 days") 
from rec;

It seems to work, but isn't there a simpler way?

Aucun commentaire:

Enregistrer un commentaire