MySQL DATE_FORMAT not correct at first week

Question

Year 2015 has 53 weeks. When your now week starts at monday are 1, 2 and 3 januari 2016 also in week 53. 4-jan-2016 is week 1.

In MySQL it output this below. But how to avoid?

SELECT DATE_FORMAT('2015-12-31', 'week %u') //week 53 
SELECT DATE_FORMAT('2016-01-01', 'week %u') //week 00 
SELECT DATE_FORMAT('2016-01-02', 'week %u') //week 00
SELECT DATE_FORMAT('2016-01-03', 'week %u') //week 00
SELECT DATE_FORMAT('2016-01-04', 'week %u') //week 01

I use PHP 7.0.9 and 10.1.16-MariaDB

Answer 1

Well, week 0 doesn't exist according to the rules for assigning weeks. One method would be a length case statement:

SELECT (CASE WHEN DATE_FORMAT('2016-01-01', 'week %u') = 'week 00' then 'week 53'
             ELSE DATE_FORMAT('2016-01-01', 'week %u')
        END)
. . .

Or, nested functions like this:

SELECT COALESCE(NULLIF(DATE_FORMAT('2016-01-01', 'week %u'), 'week 00'), 'week 53')

Answer 2

Try it with something like this:

SELECT DATE_FORMAT('2015-12-31', 'week %u') bad, WEEK('2015-12-31', 3) good UNION ALL
SELECT DATE_FORMAT('2016-01-01', 'week %u') bad, WEEK('2016-01-01', 3) good UNION ALL
SELECT DATE_FORMAT('2016-01-02', 'week %u') bad, WEEK('2016-01-02', 3) good UNION ALL
SELECT DATE_FORMAT('2016-01-03', 'week %u') bad, WEEK('2016-01-03', 3) good UNION ALL
SELECT DATE_FORMAT('2016-01-04', 'week %u') bad, WEEK('2016-01-04', 3) ;