Jump to content

Update query not working text field to date field


twiog
Go to solution Solved by twiog,

Recommended Posts

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 by twiog
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.