The STR_TO_DATE SQL Function in MySQL and MariaDB - Format string to date |
|
| | STR_TO_DATE | Syntax: | STR_TO_DATE(String, Format) | | Return value: | DATE, TIME, DATETIME | | Function type: | Date and Time function | |
| | The STR_TO_DATE() function takes a "String" after the "Format" specification and returns a value of type DATETIME, DATE or TIME depending on the content of the "Format" specification.
The date and time values contained in "String" should match the string specified by "Format".
If "String" or "Format" contains an invalid value or NULL, the STR_TO_DATE() function returns NULL.
STR_TO_DATE() is the inverse of the DATE_FORMAT() function.
Options for the "Format" string:
| Option | Description |
| %a | Short weekday name in current locale (Variable lc_time_names). |
| %b | Short form month name in current locale. For locale en_US this is one of: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov or Dec. |
| %c | Month with 1 or 2 digits. |
| %D | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...). |
| %d | Day with 2 digits. |
| %e | Day with 1 or 2 digits. |
| %f | Microseconds 6 digits. |
| %H | Hour with 2 digits between 00-23. |
| %h | Hour with 2 digits between 01-12. |
| %I | Hour with 2 digits between 01-12. |
| %i | Minute with 2 digits. |
| %j | Day of the year (001-366) |
| %k | Hour with 1 digits between 0-23. |
| %l | Hour with 1 digits between 1-12. |
| %M | Full month name in current locale (Variable lc_time_names). |
| %m | Month with 2 digits. |
| %p | AM/PM according to current locale (Variable lc_time_names). |
| %r | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
| %S | Seconds with 2 digits. |
| %s | Seconds with 2 digits. |
| %T | Time in 24 hour format. Short for '%H:%i:%S'. |
| %U | Week number (00-53), when first day of the week is Sunday. |
| %u | Week number (00-53), when first day of the week is Monday. |
| %V | Week number (01-53), when first day of the week is Sunday. Used with %X. |
| %v | Week number (01-53), when first day of the week is Monday. Used with %x. |
| %W | Full weekday name in current locale (Variable lc_time_names). |
| %w | Day of the week. 0 = Sunday, 6 = Saturday. |
| %X | Year with 4 digits when first day of the week is Sunday. Used with %V. |
| %x | Year with 4 digits when first day of the week is Sunday. Used with %v. |
| %Y | Year with 4 digits. |
| %y | Year with 2 digits. |
| %# | For str_to_date(), skip all numbers. |
| %. | For str_to_date(), skip all punctation characters. |
| %@ | For str_to_date(), skip all alpha characters. |
| %% | A literal % character. |
| SQL Examples for the STR_TO_DATE function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
| select str_to_date('2020-02-30', '%Y-%m-%d');
select str_to_date('2020, 02, 30', '%Y, %m, %d');
select str_to_date('30, 02, 2020', '%d, %m, %Y');
select str_to_date('20200230', '%Y%m%d');
select str_to_date('May 2, 2023', '%M %e, %Y');
select str_to_date('Monday, May 2, 2023', '%W, %M %e, %Y');
select str_to_date('10 15 30', '%H %i %s');
select str_to_date('30 15 10', '%s %i %H');
select str_to_date('10 15 30 6000', '%H %i %s %f');
select str_to_date('May 2, 2023', '%W, %M %e, %Y');
select str_to_date('May 2, 2023', '');
select str_to_date(null, '%W, %M %e, %Y');
select str_to_date('Monday, May 2, 2023', null);
|
|
| str_to_date('2020-02-30', '%Y-%m-%d') |
|---|
| date(10) |
|---|
| 2020-02-30 |
|
|
| str_to_date('2020, 02, 30', '%Y, %m, %d') |
|---|
| date(10) |
|---|
| 2020-02-30 |
|
|
| str_to_date('30, 02, 2020', '%d, %m, %Y') |
|---|
| date(10) |
|---|
| 2020-02-30 |
|
|
| str_to_date('20200230', '%Y%m%d') |
|---|
| date(10) |
|---|
| 2020-02-30 |
|
|
| str_to_date('May 2, 2023', '%M %e, %Y') |
|---|
| date(10) |
|---|
| 2023-05-02 |
|
|
| str_to_date('Monday, May 2, 2023', '%W, %M %e, %Y') |
|---|
| date(10) |
|---|
| 2023-05-02 |
|
|
| str_to_date('10 15 30', '%H %i %s') |
|---|
| time(10) |
|---|
| 10:15:30 |
|
|
| str_to_date('30 15 10', '%s %i %H') |
|---|
| time(10) |
|---|
| 10:15:30 |
|
|
| str_to_date('10 15 30 6000', '%H %i %s %f') |
|---|
| time(17) |
|---|
| 10:15:30.600000 |
|
|
| str_to_date('May 2, 2023', '%W, %M %e, %Y') |
|---|
| date(10) |
|---|
| NULL |
|
|
| str_to_date('May 2, 2023', '') |
|---|
| date(10) |
|---|
| NULL |
|
|
| str_to_date(null, '%W, %M %e, %Y') |
|---|
| date(10) |
|---|
| NULL |
|
|
| str_to_date('Monday, May 2, 2023', null) |
|---|
| datetime(26) |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the STR_TO_DATE() function in MySQL and MariaDB databases | | The STR_TO_DATE() function in MySQL and MariaDB converts a string representation of a date or time to an equivalent Date or time value and allows the format of the input string to be specified using format specifiers to match the pattern of the date or time string. The function returns no result if the input string doesn't match the specified format. STR_TO_DATE() is useful when a string representation of a date or time to an appropriate date or time value in MySQL or MariaDB needs to be converted. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
| | TIME_FORMAT | TIME_FORMAT(Time, Format) | | More about TIME_FORMAT Function |
|
|
| | | More information about the STR_TO_DATE SQL function: and and |
|
|
|
|
