twiog Posted September 1, 2015 Share Posted September 1, 2015 (edited) I'm using MySQL 5.5 I have a varchar 25 field called CreateDateTxt which is full of dates like this 3/15/2014 I have a date field called CreateDate which I would like to populate based on the CreateDateTxt field I can populate the CreateDate field successfully using this: UPDATE tblMembers SET CreateDate = '1975-12-07' But it doesn't give me the results I want using this: UPDATE tblMembers SET CreateDate = str_to_date(CreateDateTxt,'%d %m %Y') The query runs without errors but It populates the CreateDate field in all the records with this: 0000-00-00 Any help? Thanks! Edited September 1, 2015 by twiog Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted September 1, 2015 Share Posted September 1, 2015 Why are you storing your dates into two different fields one being varchar and the other being of date type. If you are going to store the date in a date type field then it must stored in YYYY-MM-DD format. If you attempt to store the date in a different format mysql will always truncate the date to 0000-00-00 If you want to the date to be formattted when you get it out the table you can use MySQL date_format function in your select query or use PHP's built in DateTime::format when you retrieve the date from your query. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 1, 2015 Share Posted September 1, 2015 (edited) You probably get 0000-00-00 because 3/15/2014 is m/d/y and you are trying to convert from d/m/y, so getting invalid dates. Also, you left the "/"s out of your formatting string Edited September 1, 2015 by Barand Quote Link to comment Share on other sites More sharing options...
twiog Posted September 1, 2015 Author Share Posted September 1, 2015 Why are you storing your dates into two different fields one being varchar and the other being of date type. If you are going to store the date in a date type field then it must stored in YYYY-MM-DD format. If you attempt to store the date in a different format mysql will always truncate the date to 0000-00-00 If you want to the date to be formattted when you get it out the table you can use MySQL date_format function in your select query or use PHP's built in DateTime::format when you retrieve the date from your query. I imported the data from a CSV file which had the date field in that format. Once I update all of the dates to proper dates in the CreateDate field I will delete the CreateDateTxt field. Quote Link to comment Share on other sites More sharing options...
Solution twiog Posted September 1, 2015 Author Solution Share Posted September 1, 2015 You probably get 0000-00-00 because 3/15/2014 is m/d/y and you are trying to convert from d/m/y, so getting invalid dates. Also, you left the "/"s out of your formatting string OMGosh that did it!!! Thanks so much. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.