Update query not working text field to date field

[twiog]

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

[Ch0cu3r]

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.

[Barand]

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

[twiog]

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.

[twiog]

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.