CODEWITHSUNDEEP
mysql
shim
shim

Reputation: 23

week number returns previous year last week number for january fist week in mysql

I am facing one issue while fetching the week number for 2016-01-01(fist week) in mysql. it return 53 if i use week() function or weekofyear() function . I have to get as 1 since I am linking the weeknumber in my project in other places and if it returns 1 the next week number also should be adjusted according to that. Kindly any one help me on this.

Upvotes: 2

Views: 105

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

The manual says:

If the week containing January 1 has 4 or more days in the new year, it is week 1.

Otherwise, it is the last week of the previous year, and the next week is week 1.

So you can provide the mode to the week function.

Mode   First day of week    Range   Week 1 is the first week …
0      Sunday               0-53    with a Sunday in this year
1      Monday               0-53    with 4 or more days this year
2      Sunday               1-53    with a Sunday in this year
3      Monday               1-53    with 4 or more days this year
4      Sunday               0-53    with 4 or more days this year
5      Monday               0-53    with a Monday in this year
6      Sunday               1-53    with 4 or more days this year
7      Monday               1-53    with a Monday in this year

Something like this:

select WEEK('2016-01-01',0) + 1;

FIDDLE DEMO

Upvotes: 1

Related Questions