When to use VARCHAR and DATE/DATETIME

by

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.

More Related Contents:

  1. Current date in Oracle Db
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. Count work days between two dates
  5. Comparing date ranges
  6. Oracle’s default DATE format
  7. How to determine the number of days in a month in SQL Server?
  8. How to convert an integer (time) to HH:MM:SS::00 in SQL Server 2008?
  9. How do you do date math that ignores the year?
  10. Select data from date range between two dates
  11. Find closest date in SQL Server
  12. Query comparing dates in SQL
  13. Getting date list in a range in PostgreSQL
  14. How to compare two dates to find time difference in SQL Server 2005, date manipulation
  15. How to calculate the number of “Tuesdays” between two dates in TSQL?
  16. SQL SERVER: Get total days between two dates
  17. Get month name from date in Oracle
  18. Add business days to date in SQL without loops
  19. SQL ORDER BY date problem
  20. Comparing with date in Oracle sql
  21. Check whether string is a date Postgresql
  22. Return number from Oracle Select statement after parsing date
  23. How to compare dates in datetime fields in Postgresql?
  24. Postgresql query between date ranges
  25. Getting results between two dates in PostgreSQL
  26. Subtracting Dates in Oracle – Number or Interval Datatype?
  27. Query for count of distinct values in a rolling date range
  28. Select query with date condition
  29. Oracle Convert TIMESTAMP with Timezone to DATE
  30. Date serial in SQL?

Leave a Comment