On: What's New in SQLite 3.35

[em500]

The ISO8601 weekdate format[1] is tricky, but it can be done:

    SELECT strftime('%Y', yyyymmdd, '-3 days', 'weekday 4') || '-W' || substr('00' || (((strftime('%j', date(yyyymmdd), '-3 days', 'weekday 4')) - 1) / 7 + 1), -2, 2)  || '-' || replace(strftime('%w', yyyymmdd), '0', '7')  AS yyyywwwd ;

returns ISO weekdate format ('2021-W10-6') the when the input column `yyyymmdd` is in year-month-day format ('2021-03-13'). Not pretty, but it works. The annoying part for me is actually that '%w' returns 0 for Sunday, 1-6 for Mon-Sat, rather than the ISO 1-7 for Mon-Sun.

If sqlite supported macros, we could have put the entire expression in a new "function". But you could (ab)use m4 or even the C preprocessor for that :).

[1] https://en.wikipedia.org/wiki/ISO_week_date

[hanche → em500]

Re the annoying part, a different way to get the day of week in the rance 1–7 is

  1+(6+strftime('%w',yyyymmdd))%7