2

I have a date that sometimes can be only a year, years and month or a full date. What is the best practice to save it on the db?

I need to do search later as: Give me all date between 2004 - 2008 or give me all date between 2004-05-21 to 2005-12-31

Atomico
  • 453
  • 1
  • 6
  • 26
  • possible duplicate of [Date function to display all dates between two dates](http://stackoverflow.com/questions/14851190/date-function-to-display-all-dates-between-two-dates) – Lalit Sharma Apr 22 '15 at 11:09
  • store it as the full date. you can extract the year component as needed with `year(datefield)` – pala_ Apr 22 '15 at 11:11
  • i read that i can store the date as 2004-00-00 to save an incomplete date... this could work? – Atomico Apr 22 '15 at 11:13
  • @Atomico Try storing it as such and see what happens – Kvothe Apr 22 '15 at 12:28

1 Answers1

5

I would suggest to use DATE format (https://dev.mysql.com/doc/refman/5.5/en/datetime.html) and then if needed use more specific functions to query records by date ranges like

WHERE YEAR(created) BETWEEN 2010 AND 2011

etc.

More date\time related functions here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Jinksy
  • 421
  • 4
  • 11