STR_TO_DATE function converts a string in the specified format to DATETIME, DATE or TIME value.
Syntax | STR_TO_DATE(string, format) |
Quick Example | SELECT STR_TO_DATE('17-09-2010','%d-%m-%Y'); |
Error | Returns NULL if the format is not matched, or datetime value is not valid |
Version: MySQL 5.6
Most common format specifiers in STR_TO_DATE function (full list...):
%Y | 4-digit year | |
%y | 2-digit year | for year < 70 the century is 20th, otherwise 19th |
%b | Abbreviated month (Jan - Dec) | |
%M | Month name (January - December) | |
%m | Month (0 - 12) | Zero month supported by MySQL |
%a | Abbreviated day (Sun - Sat) | |
%d | Day (0 - 31) | Zero day supported by MySQL |
%H | Hour (0 - 23) | |
%h | Hour (01 - 12) | |
%i | Minutes (0 - 59) | |
%s | Seconds (0 - 59) |
Related functionality for STR_TO_DATE in MySQL:
DATE_FORMAT(datetime, format) | Converts a datetime value to string with the specified format |
String to datetime conversion in other databases:
Oracle:
TO_DATE(string, format) | Converts string in the specified format to DATE | Different format specifiers |
TO_TIMESTAMP(string, format) | Converts string in the specified format to TIMESTAMP |
Format specifiers in MySQL and Oracle (full list...) are different:
MySQL | Oracle | Format Specifier |
%Y | YYYY | 4-digit year |
%y | YY | 2-digit year |
%b | MON | Abbreviated month (Jan - Dec) |
%M | MONTH | Month name (January - December) |
%m | MM | Month (1 - 12) |
%a | DY | Abbreviated day (Sun - Sat) |
%d | DD | Day (1 - 31) |
%H | HH24 | Hour (0 - 23) |
%h | HH or HH12 | Hour (1 - 12) |
%i | MI | Minutes (0 - 59) |
%s | SS | Seconds (0 - 59) |
SQL Server:
CONVERT(DATETIME, string, style) | style specifies a format for the entire datetime value, not its individual parts |
MySQL formats specifiers and SQL Server styles(full list...):
MySQL | SQL Server | Example | Standard |
%m/%d/%Y | 101 | 09/17/2010 | US |
%Y.%m.%d | 102 | 2010.09.17 | ANSI |
%d/%m/%Y | 103 | 17/09/2010 | European |
%d.%m.%Y | 104 | 17.09.2010 | |
%d-%m-%Y | 105 | 17-09-2010 |
PostgreSQL:
TO_DATE(string, format) | Converts string in the specified format to DATE | Different format specifiers |
TO_TIMESTAMP(string, format) | Converts string in the specified format to TIMESTAMP |
Format specifiers in MySQL and PostgreSQL (full list...) are different:
MySQL | PostgreSQL | Format Specifier |
%Y | YYYY | 4-digit year |
%y | YY | 2-digit year |
%b | MON | Abbreviated month (Jan - Dec) |
%M | MONTH | Month name (January - December) |
%m | MM | Month (1 - 12) |
%a | DY | Abbreviated day (Sun - Sat) |
%d | DD | Day (1 - 31) |
%H | HH24 | Hour (0 - 23) |
%h | HH or HH12 | Hour (1 - 12) |
%i | MI | Minutes (0 - 59) |
%s | SS | Seconds (0 - 59) |
Converting a string to datetime:
MySQL:
SELECT STR_TO_DATE('17-09-2010','%d-%m-%Y');
Oracle:
Oracle TO_DATE and TO_TIMESTAMP functions can convert a string in the specified format. Note that the format specifiers are different (see mapping above).
SELECT TO_DATE('17-09-2010','DD-MM-YYYY') FROM dual;
SQL Server:
SQL Server CONVERT function can convert a string to DATETIME, but instead of specifying format specifiers for date/time parts, you have to specify a style for the entire value (see mapping above):
SELECT CONVERT(DATETIME, '17-09-2010', 105);
PostgreSQL:
PostgreSQL provides TO_DATE and TO_TIMESTAMP functions to convert a string in the specified format to DATE or TIMESTAMP.
The format specifiers are different from MySQL (see mapping above) but similar to Oracle:
SELECT TO_DATE('17-09-2010','DD-MM-YYYY');