How store date in MySQL database?

Question

I have date in `dd/mm/yyyy` format. How can I store it in a database, if I want to do some operations on it afterwards? For example, I must find out the rows, where `date > something`. What type I must set for the `date` field?

Accepted Answer

To store dates or times in MySQL use `date`, `datetime` or `timestamp`. I'd recommend the first two for most purposes.

To tell MySQL how to parse your date format use the STR_TO_DATE function. Here's an example:

```
CREATE TABLE table1 (`Date` Date);
INSERT INTO table1 (`Date`) VALUES (STR_TO_DATE('01/05/2010', '%m/%d/%Y'));
SELECT * FROM table1;

Date
2010-01-05
```

To format the results back into the original form look at the DATE_FORMAT function. Note that you only need to format it if you want to display it as a string using something other than the default format.

How store date in MySQL database?

Solution

Problem