CODEWITHSUNDEEP
mysql
Simon
Simon

Reputation: 23141

How store date in MySQL database?

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?

Upvotes: 9

Views: 47521

Answers (4)

Mark Byers
Mark Byers

Reputation: 837916

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.

Upvotes: 18

Bart
Bart

Reputation: 4920

use datetime or time stamp and using format functionL

http://davidwalsh.name/format-date-mysql-date_format

http://www.electrictoolbox.com/article/mysql/format-date-time-mysql/

Upvotes: 0

botmsh
botmsh

Reputation: 1366

Use date if you only care about the date and not about the exact time.

Upvotes: 3

greenimpala
greenimpala

Reputation: 3966

or just date if you don't need time information

Upvotes: 1

Related Questions