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.

Answer 2

When you'll have database with more than 2-3 million rows you'll know why it's better to use DATETIME than VARCHAR :)

Simple answer is that with databases - processing power isn't a problem anymore. Just the database size is because of HDD's seek time.

Basically with modern harddisks you can read about 100 records / second if they're read in random order (usually the case) so you must do everything you can to minimize DB size, because:

• The HDD's heads won't have to "travel" this much
• You'll fit more data in RAM

In the end it's always HDD's seek times that will kill you. Eg. some simple GROUP BY query with many rows could take a couple of hours when done on disk compared to couple of seconds when done in RAM => because of seek times.

For VARCHAR's you can't do any searches. If you hate the way how SQL deals with dates so much, just use unix timestamp in 32 bit integer field. You'll have (basically) all advantages of using SQL DATE field, you'll just have to manipulate and format dates using your choosen programming language, not SQL functions.