Skip to main content
Software Engineering

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

10
  • 31
    That's great if you're converting the data values from a single row that you can otherwise identify. What I'm talking about is when you need to /find/ rows based on Date values - say orders that are more than a year old. If your dates are stored as Character data, especially in undefined formats, this sort of simple, Date query gets tortuous and unreliable. Are your HTML controls Locale aware? If so, we're back to my '01/04/07' example and your supposedly reliable date formatting has gone right out of the window. Commented Feb 9, 2021 at 13:32
  • 1
    .. If you're parsing a date to validate, check assumptions about d/m/y versus y/m/d versus m/d/y; if the year is a leap-year, and is in a sensible range (no 10,000 BC etc). Typically, databases will allow you to store a date object as such and then permit queries like "select * from txn where tdate between sysdate and sysdate-7;" and other stuff like (paraphrasing) 'get sales which billed during business hours of /this/ client's timezone'. Commented Feb 9, 2021 at 22:22
  • 20
    @mizstereo What happens if someone needs to connect a second, non-php application to your database? What happens if you have to update this thing to another tech? Are you going to port all that conversion logic, too? It may look easy, but by storing varchar instead of date, you are slowly feeding a big, bad dog, called "Technical Debt". Someday it will break out of its pen and go right for your buttocks, and you will wish it wasn't that big... Commented Feb 10, 2021 at 0:21
  • 3
    @mizstereo: Honestly, a string in YYYY-MM-DD format is almost as good as a proper date field, since it's unambiguous and works properly in comparisons. What you still gain with a real date field is mostly that 1) you can do date arithmetic in the DB (e.g. to find all dates at most X days before today), 2) the database rejects any invalid or ill-formed dates, so you don't have to rely on the client alone for validation, and 3) you'll save a few bytes per date due to a more compact storage format, which could matter if your database was huge and had lots of dates in it. Commented Feb 10, 2021 at 11:06
  • 1
    I'm tempted to downvote; about half of the answer is invalidated if you just use an ISO 8601 date/time format. Date-as-characters is a solved problem. I suggest focusing on the advantages that the date format has over proper varchar usage rather than ways one could misuse varchar. Commented Feb 10, 2021 at 14:28