MySQL STR_TO_DATE Function - Features, Examples and Equivalents

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

MySQL STR_TO_DATE - Format Specifiers

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 in MySQL

Related functionality for STR_TO_DATE in MySQL:

DATE_FORMAT(datetime, format)

Converts a datetime value to string with the specified format

MySQL STR_TO_DATE in Other Databases

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	Different format specifiers

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	Different format specifiers

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)

MySQL STR_TO_DATE Function Conversion to Other Databases

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');

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

MySQL STR_TO_DATE Function - Features, Examples and Equivalents

MySQL STR_TO_DATE - Format Specifiers

Related Functionality in MySQL

MySQL STR_TO_DATE in Other Databases

MySQL STR_TO_DATE Function Conversion to Other Databases

Resources