Menu
I want to offer the user the possibility to introduce dates that are incomplete (for example just the year 2005, or Jan 1998) but also complete dates with year, month, day. Can I accomplish this using a single date field in the DB?
I have tried UPDATE tableA SET mydate = '2001-00-00' and it seems to work (no errors) but I'm not sure if this is correct. Internally I assume a timestamp is used so how can MySQL represent those 0's?
750
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.
Introduction to MySQL DATE data type This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want. MySQL uses 3 bytes to store a DATE value.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format.
Is it safe to store dates as a string in mysql? It is safe as long as the format that you use to represent your dates is unambiguous (that is, each value maps to a unique date). But it is always inefficient not to use the proper datatype to store a value.
There's a small note in the documentation for the DATE_FORMAT() about MySQL allowing incomplete dates:
Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.
As pointed out by @wonk0, MySQL will sets invalid dates to 0000-00-00 00:00:00 when ALLOW_INVALID_DATES is not set. However, even when ALLOW_INVALID_DATES is not set, inserting a valid year with month and day set to zero does not appear to trigger this behaviour - at least not in my testing (MySQL 5.1.54-1ubuntu4). I have used this feature before without any problem, but I have so far been unable to find any more detailed documentation that describes this behaviour in full.
Date/Time comparisons also appear to work as expected: for example, 2011-01-00 > 2011-00-00 and 2011-00-01 > 2011-00-00 do as you would expect.
UPDATE
See this answer (by another Mike) to a similar question. It points to an extract from The Definitive Guide to MySQL 5:
In older versions of MySQL, the DATE and DATETIME data types did only a limited amount of type checking. Values between 0 and 12 for months, and 0 and 31 for days were generally allowed. However, it is the responsibility of the client program to provide correct data. (For example, 0 is a permissible value for a month or day, in order to provide the possibility of storing incomplete or unknown data.)
Beginning with MySQL 5.0.2, there is a more thorough validation, so that only valid data can be stored. Still allowed are the month and day values 0, as well as the date 0000-00-00.
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
