MySQL permits you to store a zero value of '0000-00-00' as a “dummy date.”: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

I'm currently inserting into this field using STR_TO_DATE(?, "%M %e, %Y %l:%i:%s %p") where ? could be June 5, 2019 5:14:47 PM, for example.

What string can I pass to this STR_TO_DATE to get it to store zero?

  • Column has no default, but is nullable. Used to be either the date didn't exist (null) or it was known. Now I need a third option: date exists, but is unknown. "Zero" would be perfect for this (because I check this field against null all over the place), but I can't figure out what string to pass my STR_TO_DATE to get "zero". – Pascal Jun 6 '19 at 3:54
  • Thank you for the input. Due to having to change the server mode, NO_ZERO_DATE being depreciated, and "A nondate value of 0000-00-00 is just a dumb old decision.", I'm just going to use 2000-01-01 as a dummy date. – Pascal Jun 6 '19 at 17:05
  • This column records when an event happened and will never be prior to 2015. Using a "super-future" date would create a problem when the super-future arrives. – Pascal Jun 7 '19 at 16:50

Your Answer

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

Browse other questions tagged or ask your own question.