Collections:
STR_TO_DATE() - Parsing String to Datetime
How to parse string to a datetime value using the STR_TO_DATE() function?
✍: FYIcenter.com
STR_TO_DATE(str, format) is a MySQL built-in function that parses string to a datetime value according to a given format. For example:
SELECT STR_TO_DATE('01,5,2023', '%d,%m,%Y'); -- +--------------------------------------+ -- | STR_TO_DATE('01,5,2023', '%d,%m,%Y') | -- +--------------------------------------+ -- | 2023-05-01 | -- +--------------------------------------+ SELECT STR_TO_DATE('May 1, 2023', '%M %d,%Y'); -- +----------------------------------------+ -- | STR_TO_DATE('May 1, 2023', '%M %d,%Y') | -- +----------------------------------------+ -- | 2023-05-01 | -- +----------------------------------------+
Extra trailing characters are ignored in the parsing process.
SELECT STR_TO_DATE('01,5,2023XYZ', '%d,%m,%Y'); -- +-----------------------------------------+ -- | STR_TO_DATE('01,5,2023XYZ', '%d,%m,%Y') | -- +-----------------------------------------+ -- | 2023-05-01 | -- +-----------------------------------------+
Parsing a string with extra leading characters returns NULL.
SELECT STR_TO_DATE('ABC01,5,2023', '%d,%m,%Y'); -- +-----------------------------------------+ -- | STR_TO_DATE('ABC01,5,2023', '%d,%m,%Y') | -- +-----------------------------------------+ -- | NULL | -- +-----------------------------------------+
If NO_ZERO_DATE in sql_mode is enabled, zero dates are disallowed. Parsing a string with missing date or time components returns NULL.
SHOW VARIABLES LIKE 'sql_mode'; -- +---------------+----------------------------------------------------------------+ -- | Variable_name | Value | -- +---------------+----------------------------------------------------------------+ -- | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, | -- | | NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | -- +---------------+----------------------------------------------------------------+ SELECT STR_TO_DATE('2023', '%Y'), STR_TO_DATE('9', '%s'); -- +---------------------------+------------------------+ -- | STR_TO_DATE('2023', '%Y') | STR_TO_DATE('9', '%s') | -- +---------------------------+------------------------+ -- | NULL | NULL | -- +---------------------------+------------------------+
Reference information of the STR_TO_DATE() function:
STR_TO_DATE(str, format): datetime Takes a string str and a format string format and returns a DATETIME value, if the format string contains both date and time parts. It returns a DATE or TIME value if the string contains only date or time parts. Arguments, return value and availability: str: Required. The string to be parsed. format: Required. The string with format specifiers. datetime: Return value. The parsed datetime, date or time value. Available since MySQL 4. Datetime format specifiers: Specifier Description --------- ----------- %a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week %V Week (01..53), where Sunday is the first day of the week %v Week (01..53), where Monday is the first day of the week %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal % character %x x, for any “x” not listed above
⇒ SUBDATE() - Synonym for DATE_SUB()
⇐ SEC_TO_TIME() - Converting Seconds to Time
2023-11-17, 268🔥, 0💬
Popular Posts:
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
Is PL/SQL Language Case Sensitive in Oracle? PL/SQL language is not case sensitive: Reserved words a...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...