MariaDB STR_TO_DATE() Function
In MariaDB, STR_TO_DATE() is a built-in function that converts a given date string to a date/time/datetime according to a specified format string.
The STR_TO_DATE() function is the opposite of the DATE_FORMAT() function.
MariaDB STR_TO_DATE() Syntax
This is the syntax of the MariaDB STR_TO_DATE() function:
STR_TO_DATE(str, format)
Parameters
str-
Required. A string to be formatted as a date.
format-
Required. format string.
The following table organizes the formatting symbols available in format parameter:
| Symbol | Description |
|---|---|
%a |
Abbreviation of the week (Sun.. Sat) |
%b |
Abbreviation of the month (Jan.. Dec) |
%c |
month number (0.. 12) |
%D |
Day of the month with English prefixes (0th, 1st, 2nd, 3rd, …) |
%d |
Two-digit representation of day of the month (00.. 31) |
%e |
Numerical representation of each day of the month (0.. 31) |
%f |
microseconds (000000.. 999999) |
%H |
hours (00.. 23) |
%h |
hours (01.. 12) |
%I |
hours (01.. 12) |
%i |
minutes (00.. 59) |
%j |
day of the year (001.. 366) |
%k |
hours (0.. 23) |
%l |
hours (1.. 12) |
%M |
month name (January.. December) |
%m |
Two-digit month (00.. 12) |
%p |
AM or PM |
%r |
Twelve-hour time (hh:mm:ssfollowed by AM or PM) |
%S |
seconds (00.. 59) |
%s |
seconds (00.. 59) |
%T |
Twenty-four hour time ( hh:mm:ss) |
%U |
The week of the year (00..53), week starts on Sunday; WEEK() mode 0 in the function |
%u |
The week of the year (00..53), the week starts on Monday; WEEK() mode 1 in the function |
%V |
The week of the year (01..53), the week starts on Sunday; WEEK() mode 2 in the function, for %X |
%v |
The week of the year (01..53), the week starts on Monday; WEEK() mode 3 in the function, for %x |
%W |
the name of the week (Sunday.. Saturday) |
%w |
Day of the week (0=Sunday.. 6=Saturday) |
%X |
The week of the year, starting with Sunday, four digits, used for%V |
%x |
The week of the year, starting with Monday, four digits, used for%v |
%Y |
Four digit year |
%y |
Two digit year |
%% |
escape % |
%x |
x, other characters listed above |
If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'STR_TO_DATE'.
Return value
The MariaDB STR_TO_DATE() function returns a date/time/datetime.
If the date string cannot be parsed according to the given format string, the STR_TO_DATE() function will return NULL.
The STR_TO_DATE() function will return NULL if any of the arguments are NULL.
MariaDB STR_TO_DATE() Examples
Here are some common examples of the STR_TO_DATE() function.
Example 1: Convert to date
SELECT STR_TO_DATE('2022/02/01', '%Y/%m/%d');
Output:
+---------------------------------------+
| STR_TO_DATE('2022/02/01', '%Y/%m/%d') |
+---------------------------------------+
| 2022-02-01 |
+---------------------------------------+Example 2: Convert to datetime
SELECT STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S');
Output:
+---------------------------------------------------------+
| STR_TO_DATE('2022/02/01 10:10:10', '%Y/%m/%d %H:%i:%S') |
+---------------------------------------------------------+
| 2022-02-01 10:10:10 |
+---------------------------------------------------------+Conclusion
In MariaDB, STR_TO_DATE() is a built-in function that converts a given date string to a date/time/datetime according to a specified format string.