DATE_FORMAT(
)
MySQL function DATE_FORMAT() formats a date as specified in the argument. You can use a list of format specifiers given below to format a date. The ‘%’ is required before formatting specifier characters.
syntax:
DATE_FORMAT(date,format)
Arguments
:
Syntax Diagram
: MySQL
Version: 5.6
Table of format specifiers
Video presentation
:
Your browser does not support HTML5 video.
Pictorial presentation: <img src="https://www.w3resource.com/w3r_images/mysql-date_format-function.png" alt="MySQL
pictorial presentation
DATE_FORMAT() function” />
Example: MySQL function DATE_FORMAT
() select date_format(date, ‘%a %D %b %Y’) as formatted_date from table_name; Where date is the
name of the date field and formatted_date is a column alias that you can use as a column header
.
The following statement will format the specified date and time 2008-05-15 22:23:00 according to the format specifier %W %D %M %Y. Here the date has been formatted with the name of the day of the week, the day of the month with the English suffix, the name of the month and the year in number.
code:
SELECT DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%W %D %M %Y’);
Example output
: mysql> SELECT DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%W %D %M %Y’); +-+ | DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%W %D %M %Y’) | +-+ | Thursday, May 15, 2008 | +-+ 1 row altogether (0.01 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-DATE_FORMAT-function – PHP MySQL Examples | w3resource</title> <meta name=”description” content=”example-DATE_FORMAT-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>Calculation datetime using MySQL:</h2> <table class=’table table-bordered’> <tr> <th>Calculated 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 DATE_FORMAT(“2008-05-15 22:23:00”, “%W %D %M %Y”) as Calculated_Date’) as $row) { echo “<tr>”; echo “<td>” . $row[‘Calculated_Date’] . “</td>”; echo “</tr>”; } ?> </tbody></table> </div> </div> </div> </body> </html>
See the example in
the 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-date_format-function</title> </head> <body> <% try { Class.forName(“com.mysql.jdbc.Driver”).newInstance(); String host = “jdbc:mysql://localhost:3306/w3resour_bookinfo”; Connection connection = null; Instruction instruction = null; ResultSet rs = null; connection = DriverManager.getConnection(Host, “root”, “datasoft123”); statement = connection.createStatement(); String Data = “SELECT DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%W %D %M %Y’) as Calculated_Date”; rs = statement.executeQuery(Data); %> <TABLE border=”1″> <tr width=”10″ bgcolor=”#9979″> <td>Calculated date</td> </tr> <% while (rs.next()) { %> <TR> <TD><%=rs.getString(“Calculated_Date”)%></TD> </TR> <% } %> </table> <% rs.close(); statement.close(); connection.close(); } catch (Exception ex) { out.println(“Unable to connect to database.”); } %> </body> </html> Example: DATE_FORMAT()
function with
specifier (%r)
The following statement will format the specified date and time 2008-05-15 22:23:00 according to the format specifier %r. Here the function returns the time in 12-hour format followed by AM or PM.
code:
SELECT DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%r’);
Sample output
: mysql> SELECT DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%r’); +-+ | DATE_FORMAT(‘2008-05-15 22:23:00’, ‘%r’) | +-+ | 10:23:00 AM | +-+ 1 row altogether (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-DATE_FORMAT-function – php mysql examples | w3resource</title> <meta name=”description” content=”example1-DATE_FORMAT-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>Calculation datetime using MySQL:</h2> <table class=’table table-bordered’> <tr> <th>Calculated 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 DATE_FORMAT(“2008-05-15 22:23:00”, “%r”)’) as $row) { echo “<tr>”; echo “<td>” . $row[‘DATE_FORMAT(“2008-05-15 22:23:00”, “%r”)’] . “</td>”; echo “</tr>”; } ?> </tbody></table> </div> </div> </div> </body> </html>
View the example in the browser
Example: DATE_FORMAT() Using Table function
The following statement will format the specified column ‘ord_date’ of the procurement table according to the format specifier
%W %D %M %Y and display all rows. Example table: Purchase
code
:
SELECT invoice_no,ord_date, DATE_FORMAT(ord_date,’%W %D %M %Y’) FROM purchase;
Example output:
mysql> SELECT invoice_no,ord_date,DATE_FORMAT(ord_date,’%W %D %M %Y’) -> PURCHASE; +-+-+-+ | invoice_no | ord_date | DATE_FORMAT(ord_date,’%W %D %M %Y’) | +-+-+-+ | INV0001 | 2008-07-06 | Sunday, July 6, 2008 | | INV0002 | 2008-08-09 | Saturday, August 9, 2008 | | INV0003 | 2008-09-15 | Monday, 15 September 2008 | | INV0004 | 2007-08-22 | Wednesday, 22 August 2007 | | INV0005 | 2007-06-25 | Monday, 25 June 2007 | | INV0006 | 2007-09-20 | Thursday, September 20, 2007 | +-+-+-+ 6 rows together (0.09 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-DATE_FORMAT-function – PHP MySQL Samples | w3resource</title> <meta name=”description” content=”example2-DATE_FORMAT-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>A list of invoice number and order date. The last column shows the order date on the day of the week name-day of the month-month-year format:</h2> <table class=’table table-bordered’> <tr> <th>Invoice no</th><th>Order date</th><th>DATE_FORMAT(ord_date,’%W %D %M %Y’)</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,,ord_date, DATE_FORMAT(ord_date,”%W %D %M %Y”) FROM purchase’) as $row) { echo “<tr>”; echo “<td>” . $row[‘invoice_no’] . “</td>”; echo “<td>\” . $row[‘ord_date’] . “</td>”; echo “<td>” . $row[‘DATE_FORMAT(ord_date,”%W %D %M %Y”)’] . “</td>”; echo “</tr>”; } ?? > </body></table> </div> </div> </div> </body> </html> View the example
in the browser
Example: DATE_FORMAT() function with where
The following statement will format the specified column ‘ord_date’ of the procurement table according to the format specifier %W %D %M %Y and returns orders that had been placed after 2007.
Example table:
Purchase code
: SELECT invoice_no,ord_date, DATE_FORMAT(ord_date,’%W %D %M %Y’) FROM purchase WHERE DATE_FORMAT(ord_date,’ %Y’)>2007;
Sample output:
mysql> SELECT invoice_no,ord_date, DATE_FORMAT(ord_date,’%W %D %M %Y’) -> FROM purchase -> WHERE DATE_FORMAT(ord_date,’ %Y’)>2007; +-+-+-+ | invoice_no | ord_date | DATE_FORMAT(ord_date,’%W %D %M %Y’) | +-+-+-+ | INV0001 | 2008-07-06 | Sunday, July 6, 2008 | | INV0002 | 2008-08-09 | Saturday, August 9, 2008 | | INV0003 | 2008-09-15 | Monday, 15 September 2008 | +-+-+-+ 3 rows together (0.01 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>example3-DATE_FORMAT-function – PHP MySQL | w3resource</title> <meta name=”description” content=”example3-DATE_FORMAT-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>A list of invoice number and order date. The last column shows the order date on the day of the week name-day of the month-month-year format. All orders listed here are placed after the year 2007:</h2> <table class=’table table-bordered’> <tr> <th>Invoice no</th><th>Order date</th><th>DATE_FORMAT(ord_date,’%W %D %M %Y’)</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,ord_date, DATE_FORMAT(ord_date,”%W %D %M %Y”) FROM purchase WHERE DATE_FORMAT(ord_date,” %Y”)>2007’) as $row) { echo “<tr>”; echo “<td>” . $row[‘invoice_no’] . “</td>”; echo “<td>” . $row[‘ord_date’] . “</td>”; echo “<td>” . $row[‘DATE_FORMAT(ord_date, “%W %D %M %Y”)’] . “</td>”; echo “</tr>”; } ?> </tbody></table> </div> </div> </div> </body> </html> View the
example in the browser
Online Practice Editor
: All date and time functions:
Click here to view MySQL date and time functions.
Previous: DATE_ADD()
Next: DATE_SUB()
