w3resource logo


MySQL WEEKDAY function

MySQL WEEKDAY() function

Secondary Nav

WEEKDAY() function

MySQL WEEKDAY() returns the index of the day in a week for a given date (0 for Monday, 1 for Tuesday and ......6 for Sunday).

Syntax :

WEEKDAY(date)

Where date is a date.

Video Presentation

Pictorial Presentation

Pictorial Presentation of MySQL WEEKDAY() function

Example: MySQL WEEKDAY() function

The following statement will returns the index of the week for the date 2009-05-19.

SELECT WEEKDAY('2009-05-19');

Output :

mysql> SELECT WEEKDAY('2009-05-19');
+-----------------------+
| WEEKDAY('2009-05-19') |
+-----------------------+
|                     1 | 
+-----------------------+
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">
<title>example-WEEKDAY-function - php mysql examples | w3resource</title>
<meta name="description" content="example-WEEKDAY-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>Index number of the specified date 2009-05-18 in the week:</h2>
<table class='table table-bordered'>
<tr>
<th>Index number of the specified date 2009-05-18 in the 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 WEEKDAY("2009-05-19")') as $row) {
echo "<tr>";
echo "<td>" . $row['WEEKDAY("2009-05-19")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

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-weekday-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 WEEKDAY('2009-05-19')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Index number of the specified date 2009-05-18 in the week</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("WEEKDAY('2009-05-19')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : WEEKDAY() function using on table

The following statement will return the invoice_no, receive_dt and index of the week for receive_dt, after making sure that index of the week for receive_dt must be more than 0 and less than 6 (i.e. it should be from Monday through Saturday).

Sample table : purchase

  SELECT invoice_no,receive_dt,WEEKDAY(receive_dt)
      FROM purchase           
      WHERE WEEKDAY(receive_dt)>0 
      AND WEEKDAY(receive_dt)<6;

Output :

mysql> SELECT invoice_no,receive_dt,WEEKDAY(receive_dt)
    ->       FROM purchase           
    ->       WHERE WEEKDAY(receive_dt)>0 
    ->       AND WEEKDAY(receive_dt)< 6;
+------------+------------+---------------------+
| invoice_no | receive_dt | WEEKDAY(receive_dt) |
+------------+------------+---------------------+
| INV0001    | 2008-07-19 |                   5 | 
| INV0002    | 2008-08-28 |                   3 | 
| INV0003    | 2008-09-23 |                   1 | 
| INV0004    | 2007-08-30 |                   3 | 
+------------+------------+---------------------+
4 rows in set (0.06 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example1-WEEKDAY-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-WEEKDAY-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>Invoice no, Received date and WEEKDAY of received date where index of WEEKDAY of received date is between 1 to 5 (i.e. it should be Monday to Friday):</h2>
<table class='table table-bordered'>
<tr>
<th>Invoice no.</th><th>Received date</th><th>WEEKDAY of received date</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 invoice_no,receive_dt,WEEKDAY(receive_dt)
FROM purchase 
WHERE WEEKDAY(receive_dt)>0 
AND WEEKDAY(receive_dt)<6') as $row) {
echo "<tr>";
echo "<td>" . $row['invoice_no'] . "</td>";
echo "<td>" . $row['receive_dt'] . "</td>";
echo "<td>" . $row['WEEKDAY(receive_dt)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

All Date and Time Functions :

Click here to see the MySQL Date and time functions.



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.