Jump to content

Archived

This topic is now archived and is closed to further replies.

cgchris99

help concatenating date fields into real date field

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.