Splitting Data From One Field Into Others

[Manny]

I'm currently moving a load of old data from table to table and need a bit of support here:I run a football (soccer for those in the US) website and have a result archive on display. At the moment, each club my team has played has their own table and I want to combine the data in all of these into one.I know how to do that but I am having one problem with getting the rows in the right order in the new table.Here are my fields:

ID, Date, Competition, Home, Result, Away

The "Date" column has data like "01/01/2010", "02/12/1976" etc. In the UK our dates are in the D/M/Y format. Therefore, I want to split the data from the "Date" fields into three and put them into "Day", "Month", "Year" fields in the new table.How would I go about this? I could probably write a PHP script in a couple of hours but if there is something easier in SQL that does the same trick then I'd rather do that.

[ProblemHelpPlease]

Have you thoguht about using a ORBER BY ASC or DESC on the date field when searching or outputting the result of the data. Why exactly does the date field need splitting into 3 parts?

[Manny]

It's just a temporary thing while I move the data from one place to another. I have about a hundered different tables that need sorting into one.At present the data is ordered by the ID. Ordering by the date column would mean 01/01/2000 would come before say, 05/01/1940 and that's not what I want. The aim is to split D/M/Y, then order by Year, Month, Day then put that result into a new table with that then being ordered by a new ID column.

[ProblemHelpPlease]

Sorry I didnt spot the data format you are using, I always use Y/M/D. I'm not aware of anything in SQL that could quickly perform what you want to do. It might be easier to write a srcipt to rearrange the dates first for each table then write another to import the data into the new database.

[Manny]

It's starting to look like the most likely option. Thanks.