[SOLVED] need help reformatting date

[thejoester]

So I have this mysql database and it has a table with a field for a date. The table holds logging info so I need to keep the current data.

 

However the date format was entered in with single digits for date. the exact format was used in php as date("n.j.Y h:i a") so june 2nd 2009 looks like "6.2.2009 12:00 am"

 

now the problem is when the dates are sorted, it messes up the order so 6.25.2009 shows up after 6.3.2009 when sorted in descending order.

 

is there a way to convert this date string to either the date / time format used by mysql or to the format of date("n.d.Y h:i a") using PHP?

 

 

[dzelenika]

What is type of Your date field?

If You are using DATETIME data type dates should be correctly sorted.

There are many solutions of your problem which depends of Your specific situation.

If You post the code and table structure I think I could help You ...

[PFMaBiSmAd]

so I need to keep the current data

Why do you think that?

 

Use a DATETIME column and just format the date as YYYY-MM-DD HH:MM:SS when it is inserted and use the mysql DATE_FORMAT() function when you retrieve the data if you need it in the format you have shown.

 

If you need to re-format the existing data to copy it into a DATETIME column, just use the mysql STR_TO_DATE() function in a single UPDATE query (without a WHERE clause.)

[thejoester]

The mysql field is setup as a VARCHAR(50) and the date has been being filled in to it by PHP instead of using a DATETIME field and using the NOW() function which I would prefer

 

This is a site that I took over and the person who hacked it together took a lot of shortcuts and I am trying to un-do them.

 

If I make a new field in the table, name it something like new_date or something and set it to the DATETIME format, how do I convert a date in the format of "MM.D.YYYY HH:MM A" to the YYYY-MM-DD HH:MM:SS format?

 

going forwards it will be easy to store this data correctly, but I need to use all the previous data if possible.

[PFMaBiSmAd]

If you need to re-format the existing data to copy it into a DATETIME column, just use the mysql STR_TO_DATE() function in a single UPDATE query (without a WHERE clause.)

[thejoester]

If you need to re-format the existing data to copy it into a DATETIME column, just use the mysql STR_TO_DATE() function in a single UPDATE query (without a WHERE clause.)

 

wouldnt that overwite all dates to the same date if I do not use a WHERE clause?

[PFMaBiSmAd]

No, because the STR_TO_DATE() function would get the existing date from the column it is in and put the DATETIME value produced into the new column -

 

UPDATE your_table SET new_date = STR_TO_DATE(old_date_column_here, 'you will need the format string to match your exist data');

 

[thejoester]

Thanks for all the help! This led me to a rsolution!