MySQL STR_TO_DATE() Function
Overview
SQL (Structured Query Language) is used by MySQL for managing and manipulating operations in the database. Sometimes, the user needs to convert the given string data into the format of date and time. The MySQL STR_TO_DATE() function is used to perform these conversion operations from string to date and time. MySQL string-to-date function is used in the case we have some string value and it needs to be converted and show the given string value as a date value. In this article, we will discuss what is a MySQL STR_TO_DATE() function, its parameter, and syntax, and will discuss some examples with explanations for a better understanding of the MySQL STR_TO_DATE() function.
Syntax of MySQL STR_TO_DATE() Function
The syntax of the MySQL STR_TO_DATE() function is as follows:
In the syntax of MySQL STR_TO_DATE() function, there are two arguments. The first argument is string and the second argument is format. A string is defined as the argument that needs to be converted into the date format. And format argument is defined as the format in which the string value will be converted in the MySQL string-to-date function.
Parameters of MySQL STR_TO_DATE() Function
There are two parameters of the MySQL STR_TO_DATE() function which are described below:
- string : It is defined as the parameter which consists of the string value. It will be converted into date format using MySQL string-to-date function. It s a required parameter.
- format : It is defined as the parameter which consists of a particular date format in which the above parameter will be converted. It is also a required parameter. The format of the date should be in the correct format so that the MySQL string-to-date function can work without any errors. Various format values are described using the table below.
| Value | Description |
|---|---|
| %a | It is used to define week names (Sat to Sun) |
| %b | It is used to define the name of a month (Jan to Dec) |
| %c | It is used to define the month name as a numeric value (0 to 12) |
| %D | It is used to define the day of the month using numeric values. It is followed by suffix (1st, 2nd, etc) |
| %d | It is used to define the day of the month using numeric value |
| %e | It is used to define the day of the month using numeric values (0 to 31) |
| %f | It is used to define microseconds (000000 to 999999) |
| %H | It is used to define hour (00 to 23) |
| %h | It is used to define (00 to 12) |
| %I | It is used to define (00 to 12) |
| %i | It is used to defining Minutes (00 to 59) |
| %j | It is used to define the day of the year (001 to 366) |
| %k | It is used to define Hour (0 to 23) |
| %l | It is used to define Hours (1 to 12) |
| %M | It is used to define the name of the month |
| %m | It is used to define the name of the month using numeric value |
| %p | It is used to define AM or PM |
| %r | It is used to define time in 12-hour AM or PM (hh:mm |
| %S | It is used to define Seconds (00 to 59) |
| %s | It is used to define seconds (00 to 59) |
| %T | It is used to define time in 24-hour format (hh:mm |
| %U | It is used to define the week in which Sunday will be the first day of the week |
| %u | It is used to define the week in which Sunday will be the first day of the week (00 to 53) |
| %W | It is used to define the name of the week in full (Sunday to Saturday) |
| %w | It is used to define the day of the week using numeric value (Sunday=0) |
| %X | It is used to define the year for the week in which Sunday will be the first day of the week |
| %x | It is used to define the year for the week in which Monday will be the first day of the week |
| %Y | It is used to define year using 4 digits numeric value |
| %y | It is used to define year using 2 digits numeric value |
Return Value of MySQL STR_TO_DATE() Function
The return value of MySQL STR_TO_DATE() function is a date that is specified by a format mask. The return value is the combined result of both the parameters of MySQL STR_TO_DATE() function which is string and format. The return value of the STR_TO_DATE() Function is in the format of "YYYY:MM
". But the user can also modify the format of the date as per their choice. If the string parameter is empty then the MySQL STR_TO_DATE() Function returns the output as "0000-00-00".How does the MySQL STR_TO_DATE() Function Work?
MySQL is a relational database management system (RDBMS) that is used to perform various operations on the datasets such as adding data, updating data, and manipulating the existing data in the database. There are various function that is used in MySQL to perform some operation easily and efficiently. The MySQL STR_TO_DATE() Function accepts two parameters in their function. When we use this function to convert this string into date format, it takes the string value and then converts it into the specified format (such as %a, %X, etc) that we provide in the format parameter. And then returns the output in the specified format that we have provided in the parameter of MySQL string-to-date function. In this way, the MySQL STR_TO_DATE() function works.
Examples
Now, let us see some examples of the MySQL STR_TO_DATE() function. All the examples are provided with output and explanations for better understanding.
- Example 1. Convert '15, 08, 2016' into date using MySQL STR_TO_DATE() function in the format ‘%d, %m, %Y’.
Query:
Output :
Explanation : In this example, we have used the MySQL STR_TO_DATE() function to convert a string value '15, 08, 2016' into date. We have specified the date format as ‘%d, %m, %Y’. When passing these arguments as the parameter of the function, we get the converted return value as a formatted date.
- Example 2. Convert '2023' into date using MySQL STR_TO_DATE() function in the format ‘%Y’.
Query:
Output:
Explanation : In this example, we have used the MySQL STR_TO_DATE() function to convert a string value '2023', into date. We have specified the date format as ‘%Y’. When passing these arguments as the parameter of the function, we get the converted return value as a formatted date.
- Example 3. Convert '111211' into time value using MySQL STR_TO_DATE() function in the format ‘%h%i%s’.
Query:
Output:
Explanation: In this example, we have used the MySQL STR_TO_DATE() function to convert a string value '111211', into a time value. We have specified the date format as ‘%h%i%s’. When passing these arguments as the parameter of the function, we get the converted return value as a formatted date.
- Example 4. Convert some value into date using MySQL STR_TO_DATE() function when the string parameter is empty.
Query:
Output:
Explanation : In this example, we have used the MySQL STR_TO_DATE() function to convert an empty string parameter to a date value. Since we have not specified any date format. When passing these arguments as the parameter of the function, we get the '00:00:00' value as a formatted date. This happens because the string parameter is empty so, the function always returns this default value as a result.
- Example 5. Convert '20100212 103545' into date time value using MySQL STR_TO_DATE() function in the format ‘%Y%m%d %h%i%s’.
Query:
Output:
Explanation : In this example, we have used the MySQL STR_TO_DATE() function to convert a string value '20100212 103545', into date and time value. We have specified the date and time format as ‘%Y%m%d %h%i%s’. When passing these arguments as the parameter of the function, we get the converted return value as a formatted date.
Conclusion
- MySQL STR_TO_DATE() function is used to perform this conversion operation from string to date and time.
- The syntax of the MySQL STR_TO_DATE() function is STR_TO_DATE(string, format).
- String parameter is defined as the parameter which consists of the string value. It will be converted into date format using the MySQL string-to-date function. It s a required parameter.
- Format parameter is defined as the parameter which consists of a particular date format in which the string parameter will be converted. It is also a required parameter.
- The return value of MySQL STR_TO_DATE() function is a date that is specified by a format mask.