mysql date input

Question

I have a database table--> date field and the date format is yyyy-mm-dd

if i input in anouther format then field show 0000-00-00

what is the problem and how i can overcome this whereas i have use no form encode method??

Answer 1

I would recommend using int(32) for your date field and storing unixtime. That way you can format your date into any type of string you want to display.

UNIX_TIMESTAMP()

It's called and the you can do this in your query.

INSERT INTO `table` (`date`) VALUES (UNIX_TIMESTAMP());

You can parse unixtime in any language, and many (like PHP) have built it parsing of unixtime.

date("Y-m-d", $row["time"]); // YYYY-MM-DD

Answer 2

Insert dates in YYYY-MM-DD (ISO format) and when retrieving the date, you may convert it to any format you like using:

DATE_FORMAT()

Reference:

DATE_FORMAT(date,format).