[MYSQL] STR_TO_DATE return null value

[DNK.it]

[SOLVED] [MYSQL] STR_TO_DATE return null value

Hi all,
I need to select from my database only the yesterday value. The problem is that the value data is set to VARCHAR so I've used STR_TO_DATE to convert it:

SELECT data_string_field FROM table
WHERE STR_TO_DATE(data_string_field, "%m/%d/%Y") = DATE_ADD( CURDATE(), INTERVAL -1 DAY );

but this string return null value instead of yesterday values ;(

any help will be appreciated

 

[Kate]

Make sure that the string values are in the expected format, and that there are no invalid dates like 31-Feb.
PS: it would be a good idea to convert that field to date type to avoid manipulations.

 

[DNK.it]

Make sure that the string values are in the expected format, and that there are no invalid dates like 31-Feb.
PS: it would be a good idea to convert that field to date type to avoid manipulations.

Thanks for the reply Kath, rep+.
I've solved the issue
The date field is printed as 04/23/2008 , but is stored in db as 2008-04-23, so I've only changed "%m/%d/%Y" in "%Y-%m-%d" :p

Thanks,
Antonio