sqlitestrftimeweek-number

SQLite return wrong week number for 2013?

I have a simple SQL for calculating week number in my reports on SQLite

SELECT STRFTIME('%W', 'date_column')

It was correct for 2009-2012. In 2013 I got always the wrong week number.

For example

SELECT STRFTIME('%W', '2012-02-28')

return '09' and this is correct.

SELECT STRFTIME('%W', '2013-02-28')

return '08' and this is wrong. We have the 9th week.

Is there something in SQLite date time functions that I don't understand? Or is it a bug of SQLite?

Solution

  • CL's answer works fine for OP's definition of "right", which is not quite the same as ISO definition. ISO week numbers are always in the range 1-53 (no week 0), and the last 3 days of a year may fall into Week 1 of the following year, just like the first 3 days may fall into Week 52 or 53 of the preceding year. To take these corner cases into account, you need to do something like:

    SELECT
    (strftime('%j', date(MyDate, '-3 days', 'weekday 4')) - 1) / 7 + 1 AS ISOWeekNumber
FROM MyTable;

    As a side note, SQLite's Date and Time documentation does link to the POSIX strftime man page, which defines %W modifier as: "week number of the year (Monday as the first day of the week) as a decimal number [00,53]. All days in a new year preceding the first Monday are considered to be in week 0."