SQL Update

[Samuz]

I'm not sure if this is explicitly a MYSQL situation so please move the thread to that forum if it is.

 

Anyway I have a table which was storing dates in this format:

 

'3/31/2012 12:13:21 AM'

 

I can currently convert it so that it stores the data in a more british format, with no time values:

 

'31/03/2012'

 

My problem is I have about 100,000 rows of data in my table, all with the American format & timestamp.

 

Can anyone suggest how I will convert & update all those rows into the English format above?

 

I did abit of googling before hand and I came across some of the MYSQL functions, but i'm not too sure how i'd implement those in an update query?

 

I'd appreciate if anyone could share some advice please.

[Pikachu2000]

You don't. You store them in a DATETIME field, in YYYY-MM-DD hh:mm:ss format, then format the date as you need it when you retrieve and display it, using MySQL's DATE_FORMAT() function.

[PFMaBiSmAd]

You should be storing date or date/time values using mysql's native DATE or DATETIME data types, which have formats of YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.

 

You can add a DATE data type column to your table and populate it using one UPDATE query that uses the mysql STR_TO_DATE() function to read the existing column values and produce mysql DATETIME value. Since your existing values have the time, you would also need to use the mysql DATE() function to only get the date part.

 

ref: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

[Samuz]

Yeah I hear you guys, but the problem remains because of how the data was initially being stored. That's what I need to convert into a more SQL friendly format.

[PFMaBiSmAd]

You must have missed this -

You can add a DATE data type column to your table and populate it using one UPDATE query that uses the mysql STR_TO_DATE() function to read the existing column values and produce mysql DATETIME value. Since your existing values have the time, you would also need to use the mysql DATE() function to only get the date part.

 

ref: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

[Samuz]

You must have missed this -

You can add a DATE data type column to your table and populate it using one UPDATE query that uses the mysql STR_TO_DATE() function to read the existing column values and produce mysql DATETIME value. Since your existing values have the time, you would also need to use the mysql DATE() function to only get the date part.

 

ref: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Ha sorry about that.

 

I'll have a go doing that.

 

Thanks!