How do I insert a date into a MySQL table

Question
How do I add a date and/or time into a MySQL table?
Answer:
There are 3 field types in MySQL that specificially deal with dates and times. They are DATE, DATETIME and TIMESTAMP. DATE only stores the date whereas DATETIME and TIMESTAMP also store a time as well. TIMESTAMP is referenced with UNIX epoch time and can only store dates from 1970 until 2037. If you require dates outside this range, then you should use DATETIME.

DATETIME columns store their values on the database table as 'YYYY-MM-DD HH:MM:SS'. DATE columns only store the date part, and TIMESTAMP generally stores data as 'YYYYMMDDHHMMSS', however this is dependent on the MySQL server that is running and the configuration settings.

Dates can be inserted with any of the following formats (the first two formats can be either a string or number).

YYYYMMDD
YYYYMMDDHHMMSS
'YYYY-MM-DD'
'YYYY-MM-DD HH:MM:SS'
INSERT INTO public_holidays values (0, 'Christmas Day', 20121225)
 
INSERT INTO public_holidays values (0, 'New Years', '2012-01-01 00:00:00')
 
INSERT INTO public_holidays values(0, 'EKKA', '2012-08-06')

Additional Notes:
You can still supply a time value when inserting into a DATE column however only the date part will be saved in the database column.

Comments

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.