ALTER column

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?
Mick

Re: ALTER column

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?
> Mick
>
You want UPDATE, a DML statement, not ALTER a DDL statement:

UPDATE rhinos set `Date`=CONCAT(`Date`," 2006");

But your longer term objective of changing field `date` to type 'date'
is a much more difficult task. In general, you can not change the type
of a column, so this will require an export, drop, create, import
sequence. Further external date representations are of the form
'yyyy/mm/dd' or 'mm/dd/yyyy', depending on your RDBM and its settings;
in particular, 'Mmm dd yyyy' is generally not allowed.

Re: ALTER column

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

Re: ALTER column

Bob Stearns wrote:

> mick white wrote:

[snip]
>>

>> 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?
>> Mick
>>
> You want UPDATE, a DML statement, not ALTER a DDL statement:
>
> UPDATE rhinos set `Date`=CONCAT(`Date`," 2006");
>
> But your longer term objective of changing field `date` to type 'date'
> is a much more difficult task. In general, you can not change the type
> of a column, so this will require an export, drop, create, import
> sequence. Further external date representations are of the form
> 'yyyy/mm/dd' or 'mm/dd/yyyy', depending on your RDBM and its settings;
> in particular, 'Mmm dd yyyy' is generally not allowed.

Thanks, Bob, that did the trick.
Mick

Re: ALTER column

Joe Makowiec wrote:

> 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).

Thanks, Joe, email en route.
Mick