sql - When to use VARCHAR and DATE/DATETIME

Question

We had this programming discussion on Freenode and this question came up when I was trying to use a VARCHAR(255) to store a Date Variable in this format: D/MM/YYYY. So the question is why is it so bad to use a VARCHAR to store a date. Here are the advantages:

1. Its faster to code. Previously I used DATE, but date formatting was a real pain.
2. Its more power hungry to use string than Date? Who cares, we live in the Ghz era.
3. Its not ethically correct (lolwut?) This is what the other user told me...

So what would you prefer to use to store a date? SQL VARCHAR or SQL DATE?

Answer 1

Why not put screws in with a hammer?

Because it isn't the right tool for the job.

Some of the disadvantages of the VARCHAR version:

• You can't easily add / subtract days to the VARCHAR version.
• It is harder to extract just month / year.
• There is nothing stopping you putting non-date data in the VARCHAR column in the database.
• The VARCHAR version is culture specific.
• You can't easily sort the dates.
• It is difficult to change the format if you want to later.
• It is unconventional, which will make it harder for other developers to understand.
• In many environments, using VARCHAR will use more storage space. This may not matter for small amounts of data, but in commercial environments with millions of rows of data this might well make a big difference.

Of course, in your hobby projects you can do what you want. In a professional environment I'd insist on using the right tool for the job.