1

What is the reason for using VARCHAR datatype on a column storing values in date and time 2020-01-01 11:04:37 instead of using DATETIME datatype in SQL database table?

7
  • The only possible reason may be necessity to store incorrect datetime - for example, zero as day number, or "anyvalue" placeholder in month... – Akina Jan 23 '20 at 11:42
  • @Akina The only possible? I can think of other reasons. – Colin 't Hart Jan 23 '20 at 11:51
  • @Colin'tHart I do not see any another reason for to store dates in string type field in static tables. If you may find another cases, I would like you to describe them. – Akina Jan 23 '20 at 11:54
  • @Akina As we mentioned earlier, when importing data from untrusted sources. What about different calendar systems? – Colin 't Hart Jan 23 '20 at 11:56
  • 1
    @Akina The fact remains that as a DBA you need to be pedantic, and you used the word "only". – Colin 't Hart Jan 23 '20 at 12:40
7

There is no reason. This is wrong design, which leads to problems when you need to handle some date based filters or manipulation.

For storing date and time information, datetime data type should be ued ALWAYS.

Dealing with dates as string (varchars) leads to lot of casting, which is bad e.g. for performance.

So, answering once again your question: there's no reason at all for such ddesign.

To complete answer (from comments):

Never say never. When importing data from eg CSV or plain text files, it's often helpful to be able to import the entire file without any rejects and then perform a second step to convert data from the imported table to the final destination table. In these cases I will import all data into text fields, while the final destination table will have proper data types.

BUT this should be just intermediate step, not the way date would be stored.

0
2

Possibilites:

  • Honest mistake?
  • Excessive haste?
  • Plain ignorance?
  • Sheer laziness?
  • Rank stupidity?

IMHO, there are no Good Reasons for storing "date" values in anything other than Date Data Types.

You will always, at some point, wind up wanting to do Date "things" with these values and having them stored in the wrong Data Type (or, worse still, the wrong "format") just makes that so much more difficult (or, at the very least, so much slower).

As a famous engineer once said "... the right tool for the right job!"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.