Jim_dddd Jim_dddd - 1 month ago 17
MySQL Question

MySQL time type

CREATE TABLE blurt (blurtid integer, btime datetime );


I did this query above and I got an error.
The reason is probably because "btime" from the data is something like 1/22/12. However, datetime is something like 1-22-2012.

Which type should btime be?
Thanks guys!

Answer

The following create table statement should not cause an error:

CREATE TABLE blurt (blurtid integer,  btime datetime );

But trying to insert '1/22/12' into a datetime column will cause error. MySQL has a function called STR_TO_DATE which can help with that:

INSERT INTO blurt(1, STR_TO_DATE('1/22/12', '%m/%d/%y'))

If your dates had the format '2012-01-22' then you could insert them directly. By the way, you are making the right decision by using a date type to store your date information.

Comments