0 Replies Latest reply on Jun 18, 2009 5:28 AM by David_Powers

    Formatting dates stored in MySQL

    David_Powers Adobe Community Professional (Moderator)

      MySQL stores dates in one format only, the ISO-recommended YYYY-MM-DD HH:MM:SS. Any attempt to store dates in another format will result in the date and time being converted to 0000-00-00 00:00:00.

       

      This is not as inconvenient as it sounds, because MySQL has a large number of date and time functions.

       

      To format a MySQL date in a more human readable format, use the DATE_FORMAT() function. This takes as its first argument the name of the column that contains the date you want to format. The second argument is a string containing the formatting characters (you can find a full list by following the link at the beginning of this paragraph). Assign the result of the DATE_FORMAT() function to an alias, using the AS keyword.

      Examples

      The following examples assume you want to format the value stored in a column called start_date. The formatted date is assigned to an alias called start_formatted, which is how it appears in a recordset.

       

      To format start_date as Thursday, June 18, 2009:

      SELECT col1, col2, col3,
      DATE_FORMAT(start_date, '%W, %M %e, %Y') AS start_formatted
      FROM mytable
      

      To format start_date as Thu, 18th Jun 2009:

      SELECT col1, col2, col3,
      DATE_FORMAT(start_date, '%a, %D %b %Y') AS start_formatted
      FROM mytable
      

      When using DATE_FORMAT() - or any other MySQL function - make sure there is no space between the function name and the opening parenthesis. The following example WILL NOT WORK:

      SELECT col1, col2, col3,
      DATE_FORMAT (start_date, '%a, %D %b %Y') AS start_formatted
      FROM mytable
      

      Also make sure you use single quotes around the format string passed as the second argument to DATE_FORMAT().