[SOLVED] SQL Query (convert dates)

[Canman2005]

Hi all

 

I have a database of 5,000 records and in each record is a field called 'dob' which has dates in it, the dates are stored as DD/MM/YYYY and I need to run a query on the database to convert all date records stored in 'dob' field to YYYY-MM-DD.

 

Can anyone help?

 

Thanks

 

Ed

[Psycho]

UPDATE tableName SET dob = CONCAT_WS('-',SUBSTRING(dob,7,4), SUBSTRING(dob,4,2), SUBSTRING(dob,1,2))

 

 

EDIT: FYI: I had never used CONCAT_WS or SUBSTRING in queries before. But it took me all of a few seconds to find them and the proper usage. I tested the above, so I know it works. But, to give a litttle explanation:

 

CONCAT_WS, will concatenate several strings using the first parameter as a separator. I then used SUBSTRING to make up the parts of dob to be concatenated. The first parameter is the start character and the 2nd parameter is the number of characters to get.

[Canman2005]

Thanks, you saved me like a million hours work

[Psycho]

Well, I wasn't 100% sure it would be that simple. But, if that hadn't worked you could have create a simple PHP page to iterate through all the records and update each individually.

 

Glad it worked for you.