Geek asks and answers

Does SQLite return an invalid week number for 2013?

I have simple SQL to calculate the number of weeks in my SQLite reports

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

This was correct for 2009-2012. In 2013, I always had the wrong week number.

for instance

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

return '09' and that is correct.

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

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

Is there anything in SQLite date date functions that I don't understand? Or is it a SQLite error?

+5
source share
2 answers

CL "", , ISO. 1-53 ( 0), 3 1 , 3 52 53 . , - :

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

SQLite Date and Time POSIX strftime man, %W : " ( ) [00,53]. , , 0".

+7

SQLite ( - 7 ) ( - 4 ), SQLite 4 . , :

SELECT strftime('%W', MyDate)
       + (1 - strftime('%W', strftime('%Y', MyDate) || '-01-04'))
FROM MyTable
+3

More articles:


All Articles