About the% x% v problem of MySQL

SELECT DATE_FORMAT("2019-01-01", "%x%v") AS currentWeek;

% v the week begins on Monday, and the following results are obtained:

  • 20160101 (5)-> 201553
  • 20170101 (Sun)-> 201652
  • 20180101 (1)-> 201801
  • 20181231 (1)-> 201901
  • 20190101 (II)-> 201901

if you start on the first Monday of the year, then 16 or 17 years are correct

this result is very difficult to understand. does anyone have any doubts?

Jun.15,2022

the official website of MySQL has a table showing what is the first week of the year.% v means that the week starts on Monday

.

image

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.

back to the topic

20160101 the first week is less than 4 days in 2015, so it belongs to 2015 last week
20170101 the first week is less than 4 days in 2017, so it belongs to 2018 last week
20180101 the first week is more than 4 days in 2018, so it belongs to the first week of 2018
.
and so on

reference: MySQL: function_week
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1c30c53-482a7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1c30c53-482a7.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?