Posted by Joe Makowiec on 06/17/72 11:37

On 16 Jan 2006 in alt.php.sql, mick white wrote:

> I have a table `rhinos`
> +----------+-------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>| +----------+-------------+------+-----+---------+-------+ Day |
>| varchar(20) | YES | | NULL | | Date | varchar(20)
|
>| YES | | NULL | | AwayTeam | varchar(30) | YES | |
>| NULL | | HomeTeam | varchar(30) | YES | | NULL |
>| | Time | varchar(20) | YES | | NULL | |
>| Venue | varchar(40) | YES | | NULL | |
>| +----------+-------------+------+-----+---------+-------+
>
> I want to add " 2006" to the `Date` field;
>
> mysql> SELECT Day,Date from rhinos limit 2;
> +----------+--------+
>| Day | Date | +----------+--------+
>| Friday | Apr 21 |
>| Saturday | Apr 22 | +----------+--------+
> 2 rows in set (0.00 sec)
> mysql> ALTER TABLE rhinos set `Date`=CONCAT(`Date`," 2006");
> I've tried variations of this, to no avail.
>
> My ultimate goal is to convert the field to a real date type.
> Any pointers?

Hey, Mick!

How much data do you have in the table? If it's not much, try this:
- Dump to a CSV file
- Purge the database
- Add a date field at the end of the database
- Import the CSV file to your favorite spreadsheet
- Add a column in the spreadsheet
- To that column, you'll input the date as "2006-" # datecell (where #
is the concatenation operator and datecell is the cell with the date in
it)
- Convert months to numeric values (s/Apr/04-/)
- Re-save the file as CSV
- Import the CSV to MySQL

BTW, don't keep a separate day-of-week column in the table. That can
be derived from the date, thus is redundant, and is a source of
potential errors.

If you want me to help you with this, drop me a line (below).

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[Back to original message]