Wednesday 12 November 2014

Problems in transforming date using str_to_date function - null returned for valid date input

The str_to_date() fails by returning a null value in MySql for some specific valid date values. Unlike Teradata, where we can use the cast function to format dates also, we were using the function to format our dates in MySql.
To overcome the bug with str_to_date() function, we can use the date_format() function.

Did a comparison of the values returned by both the functions when the input is a valid date and the results are below:

Query: 
select str_to_date(trim('2013-09-10 02:30:01  '), '%Y-%m-%d %H:%m:%s'), date_format(trim('2013-09-10 02:30:01  '), '%Y-%m-%d %H:%m:%s');

str_to_date() returns a null, where date_format() returns a value.







Please share with us your comments or suggestion.