Jump to content


Photo

help concatenating date fields into real date field


  • Please log in to reply
3 replies to this topic

#1 cgchris99

cgchris99
  • Members
  • PipPipPip
  • Advanced Member
  • 37 posts

Posted 14 June 2003 - 11:25 PM

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

#2 holiks

holiks
  • Members
  • PipPipPip
  • Advanced Member
  • 83 posts

Posted 16 June 2003 - 12:39 AM

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
__________________________________________________________[br]$php = array('..a stack', '...once you pop', '...you don't stop');

#3 cgchris99

cgchris99
  • Members
  • PipPipPip
  • Advanced Member
  • 37 posts

Posted 16 June 2003 - 12:42 AM

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

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 16 June 2003 - 08:52 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users