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, 264🔥, 0💬
Popular Posts:
What are single-byte character string data types supported in SQL Server Transact-SQL? Single-byte c...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
What is test testing area for? The testing area is provided to allow visitors to post testing commen...
How To Convert Numeric Expression Data Types using the CONVERT() Function in SQL Server Transact-SQL...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...