Skip to main content

STR_TO_DATE

Description

The function converts the input datetime string into a DATETIME value based on the specified format.

Syntax

STR_TO_DATE(<datetime_str>, <format>)

Parameters

ParameterDescription
<datetime_str>Required. The input datetime string to be converted.
<format>Required. The specified datetime format string, such as %Y-%m-%d %H:%i:%s, etc. for specific format parameters, see the DATE_FORMAT documentation.

In addition, the <format> supports the following several alternative formats and interprets them to the regular format:

Alternative InputInterpret as
yyyyMMdd%Y%m%d
yyyy-MM-dd%Y-%m-%d
yyyy-MM-dd HH:mm:ss%Y-%m-%d %H:%i:%s

Return Value

  • Returns a DATETIME value representing the converted datetime.
  • If the input <datetime_str> or <format> is invalid, the function returns NULL.

Example

Convert common datetime strings to DATETIME

SELECT STR_TO_DATE('2025-01-23 12:34:56', '%Y-%m-%d %H:%i:%s'),STR_TO_DATE('2025-01-23 12:34:56', 'yyyy-MM-dd HH:mm:ss');
+---------------------------------------------------------+-----------------------------------------------------------+
| str_to_date('2025-01-23 12:34:56', '%Y-%m-%d %H:%i:%s') | str_to_date('2025-01-23 12:34:56', 'yyyy-MM-dd HH:mm:ss') |
+---------------------------------------------------------+-----------------------------------------------------------+
| 2025-01-23 12:34:56.000000 | 2025-01-23 12:34:56.000000 |
+---------------------------------------------------------+-----------------------------------------------------------+

Others

select STR_TO_DATE('200442 Monday', '%X%V %W'),STR_TO_DATE('2023','%Y');
+-----------------------------------------+---------------------------+
| str_to_date('200442 Monday', '%X%V %W') | str_to_date('2023', '%Y') |
+-----------------------------------------+---------------------------+
| 2004-10-18 | 2023-01-01 |
+-----------------------------------------+---------------------------+