splitting data in a mysql table

[matthew1471]

i have a table with date / time stored in one field, like 01-01-2008 05:46:13

how can i split this into two seperate date / time fields.

Or, if that can't be done, how can i delete the time part?

cheers

Matt

 

[emp]

Do you want to use php?

If so, get all the dates from your existing DB, use the explode function (using the space as delimiter) and write it back into two fields.

::emp::

 

[matthew1471]

ah man. i didn't even think of doing that.

nice one.

 

[thedamian]

i have a table with date / time stored in one field, like 01-01-2008 05:46:13

how can i split this into two seperate date / time fields.

Don't you love how people always answer with things like "why would you ever want to do that". Or "you shouldn't do that"

I can see why you would do that. To see compare dates, or to see what time of the day certain things happen. Usually these problems can be solved in other ways other than to split the time with the date.

here's a great little link where it shows the mysql date functions like YEAR() MONTH(), DAYOFTHEMONTH().... You get the idea.

You'll be able to do an update to other new fields with these functions, or maybe just to some SELECTs with the fields split.

MySQL date and time functions, Part 1