w3resource logo


MySQL STR_TO_DATE function

MySQL STR_TO_DATE() function

Secondary Nav

STR_TO_DATE() function

MySQL STR_TO_DATE() returns a datetime value by taking a string and a specific format string as arguments.

If the date or time or datetime value specified as string is illegal, the function returns NULL. The format specifiers have been described in DATE_FORMAT() work with this function also.

Syntax :

STR_TO_DATE(str,format);

Arguments

Name Description
str A string.
format A date format.

Video Presentation

Pictorial Presentation

Pictorial Presentation of MySQL STR_TO_DATE() function

Example: MySQL STR_TO_DATE() function

The following statement will return a valid date from the given string 18,05,2009 according to the format %d,%m,%Y.

SELECT STR_TO_DATE('18,05,2009','%d,%m,%Y');

Output :

mysql> SELECT STR_TO_DATE('18,05,2009','%d,%m,%Y');
+--------------------------------------+
| STR_TO_DATE('18,05,2009','%d,%m,%Y') |
+--------------------------------------+
| 2009-05-18                           | 
+--------------------------------------+
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-STR_TO_DATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example-STR_TO_DATE-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>Getting a valid date from the given string according to the format '%d,%m,%Y':</h2>
<table class='table table-bordered'>
<tr>
<th>Valid 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 STR_TO_DATE("18,05,2009","%d,%m,%Y")') as $row) {
echo "<tr>";
echo "<td>" . $row['STR_TO_DATE("18,05,2009","%d,%m,%Y")'] . "</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-str_to_date-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 STR_TO_DATE('18,05,2009','%d,%m,%Y')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Valid date</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("STR_TO_DATE('18,05,2009','%d,%m,%Y')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : STR_TO_DATE() function using %M %d,%Y

The following statement will return a valid date from the given string May 18, 2009 according to the format %M %d,%Y.

SELECT STR_TO_DATE('May 18, 2009','%M %d,%Y');

Output :

mysql> SELECT STR_TO_DATE('May 18, 2009','%M %d,%Y');
+----------------------------------------+
| STR_TO_DATE('May 18, 2009','%M %d,%Y') |
+----------------------------------------+
| 2009-05-18                             | 
+----------------------------------------+
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>example1-STR_TO_DATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-STR_TO_DATE-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>Gettting a valid date from the given string according to the format '%M %d,%Y':</h2>
<table class='table table-bordered'>
<tr>
<th>Valid 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 STR_TO_DATE("May 18, 2009","%M %d,%Y")') as $row) {
echo "<tr>";
echo "<td>" . $row['STR_TO_DATE("May 18, 2009","%M %d,%Y")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example : STR_TO_DATE() function using (%m/%d/%Y) format

The following statement will return a valid date from the given string 05/18/2009 according to the format %m/%d/%Y.

SELECT STR_TO_DATE('05/18/2009', '%m/%d/%Y');

Output :

mysql> SELECT STR_TO_DATE('05/18/2009', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('05/18/2009', '%m/%d/%Y') |
+---------------------------------------+
| 2009-05-18                            | 
+---------------------------------------+
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>example2-STR_TO_DATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example2-STR_TO_DATE-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>Getting a valid date from the given string according to the format '%m/%d/%Y':</h2>
<table class='table table-bordered'>
<tr>
<th>Valid 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 STR_TO_DATE("05/18/2009", "%m/%d/%Y")') as $row) {
echo "<tr>";
echo "<td>" . $row['STR_TO_DATE("05/18/2009", "%m/%d/%Y")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example : STR_TO_DATE() function using %h:%i:%s

The following statement will return a valid time from the given string 11:59:59 according to the format %h:%i:%s.

SELECT STR_TO_DATE('11:59:59','%h:%i:%s');

Output :

mysql> SELECT STR_TO_DATE('11:59:59','%h:%i:%s');
+------------------------------------+
| STR_TO_DATE('11:59:59','%h:%i:%s') |
+------------------------------------+
| 11:59:59                           | 
+------------------------------------+
1 row in set (0.00 sec)

The following statement will return NULL because the format specifier %h %i %s (HOUR MINUTE SECOND) is not compatible with the contains of the string 11:59:59 (HOUR:MINUTE:SECOND).

SELECT STR_TO_DATE('11:59:59','%h %i %s');

Output :

mysql> SELECT STR_TO_DATE('11:59:59','%h %i %s');
+------------------------------------+
| STR_TO_DATE('11:59:59','%h %i %s') |
+------------------------------------+
| NULL                               | 
+------------------------------------+
1 row in set, 1 warning (0.02 sec)

Example : STR_TO_DATE() function using %W %D %M %Y %H:%i:%s format

The following statement will return a valid datetime from the given string Monday 15th September 2008 22:23:00 according to the format %W %D %M %Y %H:%i:%s.

SELECT STR_TO_DATE('Monday 15th September 2008 22:23:00',
  '%W %D %M %Y %H:%i:%s');

Output :

 mysql> SELECT STR_TO_DATE('Monday 15th September 2008 22:23:00', '%W %D %M %Y %H:%i:%s');
+----------------------------------------------------------------------------+
| STR_TO_DATE('Monday 15th September 2008 22:23:00', '%W %D %M %Y %H:%i:%s') |
+----------------------------------------------------------------------------+
| 2008-09-15 22:23:00                                                        | 
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
 

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.