MySQL WEEK() function
WEEK() function
MySQL WEEK() returns the week number for a given date.
The argument allows the user to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If no argument is included with the function, it returns the default week format.
Syntax:
WEEK(date[,mode]);
Arguments:
| Name | Description |
|---|---|
| date | A date value. |
| mode | An integer indicating the starting of the week. |
Syntax Diagram:
MySQL Version: 5.6
The following table describes how the mode arguments work:
| 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 more than 3 days this year |
| 2 | Sunday | 1-53 | with a Sunday in this year |
| 3 | Monday | 1-53 | with more than 3 days this year |
| 4 | Sunday | 0-53 | with more than 3 days this year |
| 5 | Monday | 0-53 | with a Monday in this year |
| 6 | Sunday | 1-53 | with more than 3 days this year |
| 7 | Monday | 1-53 | with a Monday in this year |
Video Presentation:
Pictorial Presentation:
Example: MySQL WEEK() function
The following statement will return the week of the specified date 2009-05-18.
Code:
SELECT WEEK('2009-05-18');
Sample Output:
mysql> SELECT WEEK('2009-05-18');
+--------------------+
| WEEK('2009-05-18') |
+--------------------+
| 20 |
+--------------------+
1 row in set (0.02 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-week-function - php mysql examples | w3resource</title>
<meta name="description" content="example-week-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Week from 2009-05-18:</h2>
<table class='table table-bordered'>
<tr>
<th>Week from 2009-05-18</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT WEEK("2009-05-18")') as $row) {
echo "<tr>";
echo "<td>" . $row['WEEK("2009-05-18")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-week-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT WEEK('2009-05-18')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>
Week from 2009-05-18</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("WEEK('2009-05-18')")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>
Example: WEEK() function with day index
The following statement will return the week of the specified date 2009-05-18. 1 as the second argument defines that the first day of the week is assumed as Monday.
Code:
SELECT WEEK('2009-05-18',1);
Sample Output:
mysql> SELECT WEEK('2009-05-18',1);
+----------------------+
| WEEK('2009-05-18',1) |
+----------------------+
| 21 |
+----------------------+
1 row in set (0.00 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example1-week-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-week-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Week of the specified date 2009-05-18 with more than 3 days this year:</h2>
<table class='table table-bordered'>
<tr>
<th>Output</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT WEEK("2009-05-18",1)') as $row) {
echo "<tr>";
echo "<td>" . $row['WEEK("2009-05-18",1)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
Example: WEEK() function using on table
The following statement will return name of the publishers, their date of establishment, the week in which it was established as WEEK(estd), the week in which it was established as WEEK(estd,1) assuming that the week started from Monday and the week in which it was established as WEEK(estd,2) assuming that the week started from Sunday.
Sample table: publisher
Code:
SELECT pub_name,estd,WEEK(estd),
WEEK(estd,1),WEEK(estd,2)
FROM publisher;
Sample Output:
mysql> SELECT pub_name,estd,WEEK(estd),
-> WEEK(estd,1),WEEK(estd,2)
-> FROM publisher;
+------------------------------+------------+------------+--------------+--------------+
| pub_name | estd | WEEK(estd) | WEEK(estd,1) | WEEK(estd,2) |
+------------------------------+------------+------------+--------------+--------------+
| Jex Max Publication | 1969-12-25 | 51 | 52 | 51 |
| BPP Publication | 1985-10-01 | 39 | 40 | 39 |
| New Harrold Publication | 1975-09-05 | 35 | 36 | 35 |
| Ultra Press Inc. | 1948-07-10 | 27 | 28 | 27 |
| Mountain Publication | 1975-01-01 | 0 | 1 | 52 |
| Summer Night Publication | 1990-12-10 | 49 | 50 | 49 |
| Pieterson Grp. of Publishers | 1950-07-15 | 28 | 28 | 28 |
| Novel Publisher Ltd. | 2000-01-01 | 0 | 0 | 52 |
+------------------------------+------------+------------+--------------+--------------+
8 rows in set (0.17 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example2-week-function - php mysql examples | w3resource</title>
<meta name="description" content="example2-week-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Week of the specified date 2009-05-18 with more than 3 days this year:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th>
<th>Established on</th>
<th>Week of establishment</th>
<th>Week of establishment, Monday as starting day of week</th>
<th>Week of establishment, Tuesday as starting day of week</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,estd,WEEK(estd),
WEEK(estd,1),WEEK(estd,2)
FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['WEEK(estd)'] . "</td>";
echo "<td>" . $row['WEEK(estd,1)'] . "</td>";
echo "<td>" . $row['WEEK(estd,2)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
Online Practice Editor:
All Date and Time Functions:
Click here to see the MySQL Date and time functions.
Previous: UTC_TIMESTAMP()
Next: WEEKDAY()
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook