Is it a good idea to use string data type for dates in MySQL instead of using datetime data type?

Question

While implementing web applications on top of MySQL database, I'm thinking if it is a good idea to just use string data type to store dates?

For example, I can just store dates as '201110191503999' into database. Also this is convenient to query by date. For example, select * from some_table where the_date like '20111019%'

Is there any performance issue if we use string for dates? and are there any advantages for using date/datetime data type?

Thanks in advance!

Answer 1

Always use the column type for what what is needed; if you are using a date, use DATETIME, if it is a timestamp, use TIMESTAMP and so on.

Depending on in what you are coding, all the formatting of the data can be done on the actual page in whatever language you are using.

Also, you can take advantage of MySQL functions such as NOW(), rather than using the language's version and then storing it into the database.

Answer 2

If your data is of date type then store the data in a DATE (or DATETIME if you have a time element to it).

If you store dates as strings then you are asking for trouble! For example, what is to stop somebody writing a value of 'I am not a date' into you string 'date' field? Or what happens if you have '20111019' and '2011-10-19' and want them to be treated as equal? Furthermore you will be missing out on a whole raft of MySQL DATE and TIME specific functions

Never store a date as a string if you can possibly avoid it.