CODEWITHSUNDEEP
mysql
Michail Michailidis
Michail Michailidis

Reputation: 12191

MySQL date_format() - what is the difference between %U and %V flags?

I saw in the documentation of MySQL date_format() that either it returns 53 or 54 different values. As far as I know years have either 52 or 53 weeks. Where is this extra week coming from?

%U  Week where Sunday is the first day of the week (00 to 53)
%u  Week where Monday is the first day of the week (00 to 53)
%V  Week where Sunday is the first day of the week (01 to 53). Used with %X
%v  Week where Monday is the first day of the week (01 to 53). Used with %X

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

What is the difference between U and V flags then? Shouldn't U flags have a range of (00 to 52)?

Upvotes: 1

Views: 579

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37430

From what you can see when playing with dates from range 2018-01-01 (Monday) - 2018-01-07 (Sunday) you can see:

  • that Vs can return previous year week number:

SELECT DATE_FORMAT("2018-01-05", "%V");

return 53 (here week starts with Sunday, which is in 2017). %v works the same, but for above would return 0, as Monday is the first day of the week and is already in 2018.

  • Us don't have that property: for the above with %U would return 1.

Upvotes: 3

Related Questions