{"id":2908,"date":"2018-07-06T17:44:37","date_gmt":"2018-07-07T00:44:37","guid":{"rendered":"https:\/\/database.guide\/?p=2908"},"modified":"2021-01-28T14:10:34","modified_gmt":"2021-01-28T04:10:34","slug":"yearweek-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/yearweek-examples-mysql\/","title":{"rendered":"YEARWEEK() Examples &#8211; MySQL"},"content":{"rendered":"<p>In\u00a0<a href=\"https:\/\/database.guide\/what-is-mysql\/\">MySQL<\/a>, the <code>YEARWEEK()<\/code> function returns the year and week for a given date. You provide the date as an argument, and the function will return the result accordingly.<\/p>\n<p>You also have the option of specifying whether to start the week on Sunday or Monday, and whether the week should be in the range 0 to 53 or 1 to 53.<\/p>\n<p><!--more--><\/p>\n<h2>Syntax<\/h2>\n<p>You can use either of the following two forms:<\/p>\n<pre>YEARWEEK(date)\r\nYEARWEEK(date,mode)\r\n<\/pre>\n<p>Where:<\/p>\n<ul>\n<li><code>date<\/code> is the date you want the year and week number returned from.<\/li>\n<li><code>mode<\/code> is a number that specifies whether the week should start on Sunday or Monday and whether the week should be in the range 0 to 53 or 1 to 53. See the table below for the possible mode values.<\/li>\n<\/ul>\n<p>If no mode is specified, the mode is <code>0<\/code>.<\/p>\n<h2>Example 1 &#8211; Basic Usage<\/h2>\n<p>Here&#8217;s an example to demonstrate.<\/p>\n<pre>SELECT YEARWEEK('2021-01-25') As 'Result';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+\r\n| Result |\r\n+--------+\r\n| 202104 |\r\n+--------+\r\n<\/pre>\n<p>Here&#8217;s an example with a different date.<\/p>\n<pre>SELECT YEARWEEK('1999-12-25') As 'Result';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+\r\n| Result |\r\n+--------+\r\n| 199951 |\r\n+--------+\r\n<\/pre>\n<h2>Example 2 &#8211; Specify a Mode<\/h2>\n<p>If you don&#8217;t specify a second argument, the <code>YEARWEEK()<\/code> function uses <code>0<\/code> as the mode.<\/p>\n<p>However, you also have the option of supplying a second argument to specify which mode to use. Example:<\/p>\n<pre>SELECT YEARWEEK('2019-11-23', 7) AS 'Mode 7';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+\r\n| Mode 7 |\r\n+--------+\r\n| 201946 |\r\n+--------+\r\n<\/pre>\n<p>The possible mode values are as follows.<\/p>\n<table>\n<thead>\n<tr>\n<th>Mode<\/th>\n<th>First day of week<\/th>\n<th>Range<\/th>\n<th>Week 1 is the first week \u2026<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>0<\/td>\n<td>Sunday<\/td>\n<td>0-53<\/td>\n<td>with a Sunday in this year<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>Monday<\/td>\n<td>0-53<\/td>\n<td>with 4 or more days this year<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>Sunday<\/td>\n<td>1-53<\/td>\n<td>with a Sunday in this year<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>Monday<\/td>\n<td>1-53<\/td>\n<td>with 4 or more days this year<\/td>\n<\/tr>\n<tr>\n<td>4<\/td>\n<td>Sunday<\/td>\n<td>0-53<\/td>\n<td>with 4 or more days this year<\/td>\n<\/tr>\n<tr>\n<td>5<\/td>\n<td>Monday<\/td>\n<td>0-53<\/td>\n<td>with a Monday in this year<\/td>\n<\/tr>\n<tr>\n<td>6<\/td>\n<td>Sunday<\/td>\n<td>1-53<\/td>\n<td>with 4 or more days this year<\/td>\n<\/tr>\n<tr>\n<td>7<\/td>\n<td>Monday<\/td>\n<td>1-53<\/td>\n<td>with a Monday in this year<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>These are the same values that can be used with the <a href=\"https:\/\/database.guide\/week-examples-mysql\/\"><code>WEEK()<\/code><\/a> function.<\/p>\n<p>One difference between these two functions is that the <code>WEEK()<\/code> function derives its default mode from the <code>default_week_format<\/code> system variable (the default value of this variable is <code>0<\/code>). <code>YEARWEEK()<\/code> on the other hand, ignores this setting, and uses <code>0<\/code> as its default value (regardless of the <code>default_week_format<\/code> setting).<\/p>\n<h2>Example 3 &#8211; Comparison of Modes<\/h2>\n<p>Here&#8217;s a quick comparison of how you can get different results depending on the mode being used.<\/p>\n<p>The following three examples use the same code, but with three different dates. These dates are consecutive &#8211; they occur on the 5th, 6th, and 7th of January. As you can see, the results can be quite different depending on the exact date and the mode being used.<\/p>\n<h3>Date 1<\/h3>\n<pre>SET @date = '2019-01-05';\r\nSELECT \r\n  YEARWEEK(@date, 0) AS 'Mode 0',\r\n  YEARWEEK(@date, 1) AS 'Mode 1',\r\n  YEARWEEK(@date, 2) AS 'Mode 2',\r\n  YEARWEEK(@date, 3) AS 'Mode 3',\r\n  YEARWEEK(@date, 4) AS 'Mode 4',\r\n  YEARWEEK(@date, 5) AS 'Mode 5',\r\n  YEARWEEK(@date, 6) AS 'Mode 6',\r\n  YEARWEEK(@date, 7) AS 'Mode 7';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| 201852 | 201901 | 201852 | 201901 | 201901 | 201853 | 201901 | 201853 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n<\/pre>\n<h3>Date 2<\/h3>\n<pre>SET @date = '2019-01-06';\r\nSELECT \r\n  YEARWEEK(@date, 0) AS 'Mode 0',\r\n  YEARWEEK(@date, 1) AS 'Mode 1',\r\n  YEARWEEK(@date, 2) AS 'Mode 2',\r\n  YEARWEEK(@date, 3) AS 'Mode 3',\r\n  YEARWEEK(@date, 4) AS 'Mode 4',\r\n  YEARWEEK(@date, 5) AS 'Mode 5',\r\n  YEARWEEK(@date, 6) AS 'Mode 6',\r\n  YEARWEEK(@date, 7) AS 'Mode 7';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| 201901 | 201901 | 201901 | 201901 | 201902 | 201853 | 201902 | 201853 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n<\/pre>\n<h3>Date 3<\/h3>\n<pre>SET @date = '2019-01-07';\r\nSELECT \r\n  YEARWEEK(@date, 0) AS 'Mode 0',\r\n  YEARWEEK(@date, 1) AS 'Mode 1',\r\n  YEARWEEK(@date, 2) AS 'Mode 2',\r\n  YEARWEEK(@date, 3) AS 'Mode 3',\r\n  YEARWEEK(@date, 4) AS 'Mode 4',\r\n  YEARWEEK(@date, 5) AS 'Mode 5',\r\n  YEARWEEK(@date, 6) AS 'Mode 6',\r\n  YEARWEEK(@date, 7) AS 'Mode 7';\r\n<\/pre>\n<p>Result:<\/p>\n<pre>+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n| 201901 | 201902 | 201901 | 201902 | 201902 | 201901 | 201902 | 201901 |\r\n+--------+--------+--------+--------+--------+--------+--------+--------+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In\u00a0MySQL, the YEARWEEK() function returns the year and week for a given date. You provide the date as an argument, and the function will return the result accordingly. You also have the option of specifying whether to start the week on Sunday or Monday, and whether the week should be in the range 0 to &#8230; <a title=\"YEARWEEK() Examples &#8211; MySQL\" class=\"read-more\" href=\"https:\/\/database.guide\/yearweek-examples-mysql\/\" aria-label=\"Read more about YEARWEEK() Examples &#8211; MySQL\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2908","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2908","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2908"}],"version-history":[{"count":2,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2908\/revisions"}],"predecessor-version":[{"id":2910,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2908\/revisions\/2910"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}