Avoiding problems with Date format in MySQL

If you construct a string with which to populate a date column and that string does not match the expected format, you will receive error.
In this case MySQL has function str_to_date which specifies string format

mysql> update ... set birth_data = str_to_date('FEB-12-1990', '%b-%d-%Y') where ...

Formatters:

  • %a – the short weekday name: Sun, Mon,…
  • %b – the short month name: Jan, Feb,…
  • %c – the numeric month (0..12)
  • %d – the numeric day of the month (00..31)
  • %f – the number of microseconds (000000..999999)
  • %H – the hour of the day, in 24-hour format (00..23)
  • %h – the hour of the day, in 12-hour format (01..12)
  • %i – the minutes within the hour (00..59)
  • %j – the day of the year (001..366)
  • %M – the full month name (January..December)
  • %m – the numeric month
  • %p – AM or PM
  • %s – the number of seconds (00..59)
  • %W – the full weekday name (Sunday..Saturday)
  • %w – the numeric day of the week (0=Sunday..6=Saturday)
  • %Y – the four-digit year
Share

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.