Giuseppe Maxia <gmax_@_cpan_._org> wrote:
> Peter wrote:
>> I have the following fields in a database:
>>
>> year integer(4) not null default '0'
>> month integer(2) not null default '0'
>> day integer(2) not null default '0'
>> hour integer(2) not null default '0'
>> minute integer(2) not null default '0'
>>
>> and just added (using alter table) the following blank field:
>>
>> date datetime not null default '0'
>>
>> I want to use (year, month, day, hour, minute) to fill in the datetime field
>> using the MySQL monitor. How can this be done?
>>
>> Thanks!
>> Pete
>
> update tablename
> set `date` = concat(
> lpad( `year`, 4,'0' ), '-',
> lpad( `month`, 2,'0' ), '-',
> lpad( `day`, 2,'0' ), ' ',
> lpad( `hour`, 2,'0' ), ':',
> lpad( `minute`, 2,'0' ), ':00'
> );
>
> Side note: Don't call your field 'date'. It's a reserved word.
> Even though MySQL is forgiving, you can face subtle errors if you
> are not extra careful.
>
> ciao
> gmax

Hmmm. Never heard of lpad. I'll go look that up now.

Point taken with the field name. You're absolutely right; I'll change it.

A big thanks for the reply!

Pete