help concatenating date fields into real date field

[cgchris99]

I have a database that has the following fields

month (in long format like July)

dayofmonth

year

 

I need to create an update script that will take these three fields and give me a mysql date field.

 

Can anyone help with this?

 

Thanks

[holiks]

any specific reason to use 3 fields?

why not just use one?

e.g \"date_field DATETIME\" or maybe \"date_field TIMESTAMP\"

 

..date maniupualtion would be easier i think

[cgchris99]

I agree that one field is better. But for some strange reason it wasn\'t designed that way. But now is has one field.

 

Thanks

[pallevillesen]

concat ?

 

P.

 

concat(year,\'-\',month,\'-\',dayofmonth);

 

You can rewrite the long format to numeric format using the case:

(CASE month

WHEN \'January\'

THEN 1

WHEN \'February\'

THEN 2

....

THEN 12

END) as month_numeric;

 

So you\'ll end up with:

 

concat(year,\'-\',

(CASE month

WHEN \'January\'

THEN 1

WHEN \'February\'

THEN 2

....

THEN 12

END),\'-\',

dayofmonth);

 

P.