MySQL STR_TO_DATE() Function: Convert Strings to Dates
Learn how to use the MySQL `STR_TO_DATE()` function to effectively convert date strings into DATE values. This guide explains the function's syntax, provides clear examples, and shows how to use format strings to parse various date formats, enabling you to work with dates stored as text in your MySQL database.
MySQL STR_TO_DATE() Function
Definition and Usage
The STR_TO_DATE() function in MySQL converts a date string into a DATE value. You provide the string and a format string that tells MySQL how the date is structured in the string. This is essential for handling dates stored as text.
Syntax
Syntax
STR_TO_DATE(string, format)
Parameter Values
| Parameter | Description |
|---|---|
string |
Required. The date string you want to convert. |
format |
Required. A format string specifying how the date is represented in the string parameter. (See the format codes below) |
Format Codes
The format parameter uses special format codes to define the date's structure. Here's a table of commonly used codes:
| Format Code | Description |
|---|---|
%a |
Abbreviated weekday name (Sun, Mon, etc.) |
%b |
Abbreviated month name (Jan, Feb, etc.) |
%c |
Month as a number (1-12) |
%d |
Day of the month as a number (01-31) |
%e |
Day of the month as a number (1-31) |
%M |
Full month name (January, February, etc.) |
%m |
Month as a number (01-12) |
%Y |
Year as a four-digit number (e.g., 2024) |
%y |
Year as a two-digit number (e.g., 24) |
%H |
Hour (00-23, 24-hour format) |
%i |
Minutes (00-59) |
%s |
Seconds (00-59) |
%p |
AM or PM |
Many more format codes exist; consult the MySQL documentation for a complete list.
Examples
Example 1: Basic Date Conversion
This example converts the string "August 10 2017" into a DATE value.
SQL Query
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
Output
STR_TO_DATE("August 10 2017","%M %d %Y")
-------------------------------------
2017-08-10
Example 2: More Complex Date Conversion
This example demonstrates converting a string with a more complex date format.
SQL Query
SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %H,%i,%s");
Output
STR_TO_DATE("2017,8,14 10,40,10","%Y,%m,%d %H,%i,%s")
---------------------------------------------------
2017-08-14 10:40:10
Technical Details
The STR_TO_DATE() function is available from MySQL 4.0 onwards.