MySQL : STR_TO_DATE function giving null on correct output with format

Related searches

I'm trying to convert string to date in mysql. Inspite of giving correct string and format specifier, mysql is giving NULL output with warning message as Incorrect datetime value: 'XXXXX' for function str_to_date although it is working for full date string.

mysql logs

mysql> SELECT STR_TO_DATE('2018/06', '%Y/%m');
+---------------------------------+
| STR_TO_DATE('2018/06', '%Y/%m') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql> SELECT STR_TO_DATE('2018/06/01', '%Y/%m/%d');                                                                                                                                                            +---------------------------------------+
| STR_TO_DATE('2018/06/01', '%Y/%m/%d') |
+---------------------------------------+
| 2018-06-01                            |
+---------------------------------------+
1 row in set (0.00 sec)

debug info

MySQL version - 5.7.21 MySQL Community Server (GPL)

mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2018/06' for function str_to_date |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

Requirement

I want to parse month and year from above date string.

For fulfilling my requirement, I'm using following workaround -

mysql> SELECT YEAR(STR_TO_DATE(CONCAT('2018/06', '/01'), '%Y/%m/%d'));
+---------------------------------------------------------+
| YEAR(STR_TO_DATE(CONCAT('2018/06', '/01'), '%Y/%m/%d')) |
+---------------------------------------------------------+
|                                                    2018 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(STR_TO_DATE(CONCAT('2018/06', '/01'), '%Y/%m/%d'));
+----------------------------------------------------------+
| MONTH(STR_TO_DATE(CONCAT('2018/06', '/01'), '%Y/%m/%d')) |
+----------------------------------------------------------+
|                                                        6 |
+----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL STR_TO_DATE() function, In mysql STR_TO_DATE function covering description, syntax, returns a datetime value by taking a string and a specific format string as value specified as a string is illegal, the function returns NULL. Sample Output: The STR_TO_DATE () function returns a date based on a string and a format. STR_TO_DATE ( string, format) Parameter Values. Required. The string to be formatted to a date. Required. The format to use. Can be one or a combination of the following values: Abbreviated weekday name (Sun to Sat) Abbreviated month name (Jan to Dec)

You can use DATE_FORMAT function in mysql

SELECT DATE_FORMAT('2018/06/01', '%Y-%m')

NB: The date string should be in any valid MYSQL DATE FORMAT(Year month day)

mysql: MySQL : STR_TO_DATE function giving null on correct , Inspite of giving correct string and format specifier, mysql is giving NULL output with warning message as Incorrect datetime value: 'XXXXX' for function  Incorrect parameter count in the call to native function 'STR_TO_DATE' – Johnny B Dec 4 '12 at 18:56 Oh yes, I forgot the format parameter. Just add a date type format if you expect dt to have a date.

If your date string is always in 'yyyy/mm' format

instead of using date function you can use substring OR substring_index function in MYSQL.

 select substring("2018/06",1,4) AS year, substring("2018/06",6,2) as month 

OR

select substring_index("2018/06","/",1) AS year, substring_index("2018/06","/",-1) as month 

44891: str_to_date("I'm_not_a_valid_date","I , If specify format which produce meaningful output result is correct: mysql> select If no date can be extracted then NULL should be returned. This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string  How to load date data in MySQL when using LOAD DATA. Your format string for STR_TO_DATE() MySQL load NULL values from CSV data.

Converting string to date format by using str_to_date() function to , By using the function str_to_date() we can convert the string data to mysql If the format is not supported by the string data given then the output will be NULL. This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.

MySQL STR_TO_DATE() Function, If the input string is illegal, the STR_TO_DATE() function returns NULL. The STR_TO_DATE() function scans the input string to match the format string. The DATE_FORMAT function returns a string whose character set and collation depend on the settings of the client’s connection. The following table illustrates the specifiers and their meanings that you can use to construct a date format string: Three-characters abbreviated weekday name e.g., Mon, Tue, Wed, etc.

The FORMAT function accepts three arguments: The N is the number that you want to format. The D is the number of decimal places that you want to round. The locale is an optional argument that determines the thousand separators and grouping between separators. If you omit the locale operator, MySQL will use en_US by default. The following link provides all locale names supported by MySQL. MySQL FORMAT function examples

Comments
  • it's in the manual... "This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning. " "2018/06" is not a valid DATE..
  • @RaymondNijland Above statement is conflicting with this statement from manual - Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0. Check out manual here
  • "Above statement is conflicting with this statement from manual" No the docs are conflicting with MySQL's execution.. @Vivek check this the statement is copied directly from the manual db-fiddle.com/f/jPKVi2wpFyKFfQH4XLzemR/1 might because off sql_mode NO_ZERO_IN_DATE .. yes this confirms it db-fiddle.com/f/jPKVi2wpFyKFfQH4XLzemR/1
  • Mine last comment also answers this question why SELECT STR_TO_DATE('2018/06', '%Y/%m') does not work it's because off the NO_ZERO_IN_DATE sql_mode.
  • I want to extract year and month from date string, how your solution will help me get that, please explain.
  • If your date string is always in 'yyyy/mm' format, instead of using date function why cant you use substring function in mysql.
  • If I'm using date function then, I can use any date formatter function like MONTHNAME to get the name of month and from substring, it won't be possible.