{"id":11312,"date":"2023-10-16T22:22:39","date_gmt":"2023-10-17T05:22:39","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=11312"},"modified":"2023-10-16T22:24:34","modified_gmt":"2023-10-17T05:24:34","slug":"mysql-yearweek","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-yearweek\/","title":{"rendered":"MySQL YEARWEEK() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL YEARWEEK()<\/code> function to return year and week for a date.<\/p>\n\n\n\n

Introduction to the MySQL YEARWEEK() function<\/h2>\n\n\n\n

The YEARWEEK()<\/code> function returns the year and week for a date<\/a>. <\/p>\n\n\n\n

Here’s the syntax of the YEARWEEK()<\/code> function:<\/p>\n\n\n

YEARWEEK(date)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  • date<\/code>: This is the date from which you want to extract the year and week.<\/li>\n<\/ul>\n\n\n\n
    The YEARWEEK<\/code> returns a value in the format yw<\/code> format. e.g., 202312<\/code>, 2023 <\/code>is the year and 12<\/code> is the week number. The YEARWEEK()<\/code> function returns NULL<\/code> if the date is NULL<\/code>.<\/p>\n\n\n\n
    The YEARKWEEK()<\/code> function also accepts a second argument:<\/p>\n\n\n
    YEARWEEK(date,mode)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax, the mode<\/code> argument specifies whether the return value should start in the range from 0 to 53 or from 1 to 53. <\/p>\n\n\n\n
    If you omit the mode<\/code> argument, the YEARWEEK()<\/code> function uses the value of the system variable @@default_week_format<\/code>:<\/p>\n\n\n
    SELECT<\/span> @@default_week_format;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------------------+<\/span>\n| @@default_week_format |\n+-----------------------+<\/span>\n|                     0 |\n+-----------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output shows that the default week format is 0, meaning that the first day of the week is Sunday, and the range for the week is from 0 to 53.<\/p>\n\n\n\n
    The following table shows all the valid modes that you can use:<\/p>\n\n\n\n
    Mode<\/th>First Day of Week<\/th>Range<\/th>Week 1 is the first week when\u2026<\/th><\/tr><\/thead>
    0<\/td>Sunday<\/td>0-53 with Sunday in this year<\/td>Any Sunday in the year<\/td><\/tr>
    1<\/td>Monday<\/td>0-53 with 4 or more days this year<\/td>At least 4 days fall within the year<\/td><\/tr>
    2<\/td>Sunday<\/td>1-53 with Sunday in this year<\/td>Any Sunday in the year, starting from week 1<\/td><\/tr>
    3<\/td>Monday<\/td>1-53 with 4 or more days this year<\/td>At least 4 days fall within the year, starting from week 1<\/td><\/tr>
    4<\/td>Sunday<\/td>0-53 with 4 or more days this year<\/td>Any Sunday in the year, starting from week 1<\/td><\/tr>
    5<\/td>Monday<\/td>0-53 with Monday in this year<\/td>Any Monday in the year<\/td><\/tr>
    6<\/td>Sunday<\/td>1-53 with 4 or more days this year<\/td>At least 4 days fall within the year, starting from week 1<\/td><\/tr>
    7<\/td>Monday<\/td>1-53 with Monday in this year<\/td>Any Monday in the year, starting from week 1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    MySQL YEARWEEK function examples<\/h2>\n\n\n\n
    We’ll take some examples of using the MySQL YEARWEEK()<\/code> function.<\/p>\n\n\n\n
    1) Simple YEARWEEK() function example<\/h3>\n\n\n\n
    The following example uses the YEARWEEK()<\/code> function to get the year and week of the date ‘2023-10-17’:<\/p>\n\n\n
    SELECT<\/span> YEARWEEK<\/span>('2023-01-01'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +------------------------+<\/span>\n| YEARWEEK('2023-01-01') |\n+------------------------+<\/span>\n|                 202301 |\n+------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returns 202342<\/code> where 2023<\/code> is the year and 01<\/code> is the week number.<\/p>\n\n\n\n
    2) The YEARWEEK() function example with the mode argument<\/h3>\n\n\n\n
    The following example uses the YEARWEEK()<\/code> function with the mode 3. It returns a different result based on the rule of the mode 3:<\/p>\n\n\n
    SELECT<\/span> YEARWEEK<\/span>('2023-01-01'<\/span>,3<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +--------------------------+<\/span>\n| YEARWEEK('2023-01-01',3) |\n+--------------------------+<\/span>\n|                   202252 |\n+--------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL YEARWEEK()<\/code> function to return year and week for a date.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t