Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Converting Dates to Character Strings

By: FYIcenter.com

(Continued from previous topic...)

How To Convert Dates to Character Strings?

You can convert dates to character strings using the DATE_FORMAT(date, format) function. MySQL supports the following basic formatting codes:

  • %a Abbreviated weekday name (Sun..Sat)
  • %b Abbreviated month name (Jan..Dec)
  • %c Month, numeric (0..12)
  • %D Day of the month with English suffix (0th, 1st, 2nd, 3rd,...)
  • %d Day of the month, numeric (00..31)
  • %e Day of the month, numeric (0..31)
  • %f Microseconds (000000..999999)
  • %H Hour (00..23)
  • %h Hour (01..12)
  • %I Hour (01..12)
  • %i Minutes, numeric (00..59)
  • %j Day of year (001..366)
  • %k Hour (0..23)
  • %l Hour (1..12)
  • %M Month name (January..December)
  • %m Month, numeric (00..12)
  • %p AM or PM
  • %r Time, 12-hour (hh:mm:ss followed by AM or PM)
  • %S Seconds (00..59)
  • %s Seconds (00..59)
  • %T Time, 24-hour (hh:mm:ss)
  • %W Weekday name (Sunday..Saturday)
  • %w Day of the week (0=Sunday..6=Saturday)
  • %Y Year, numeric, four digits
  • %y Year, numeric (two digits)

The tutorial exercise below shows you some good examples:

SELECT DATE_FORMAT('1997-01-31', '%W, %M %e, %Y') FROM DUAL;
   Friday, January 31, 1997

SELECT DATE_FORMAT('1997-01-31 09:26:50.000123', 
   '%W, %M %e, %Y, at %h:%i:%s %p') FROM DUAL;
   Friday, January 31, 1997, at 09:26:50 AM                                   |

SELECT DATE_FORMAT('1997-01-31 09:26:50.000123', 
   '%d-%b-%Y %H:%i:%s.%f') FROM DUAL;
   31-Jan-1997 09:26:50.000123

(Continued on next topic...)

  1. What Are Date and Time Data Types?
  2. How To Write Date and Time Literals?
  3. How To Enter Microseconds in SQL Statements?
  4. How To Convert Dates to Character Strings?
  5. How To Convert Character Strings to Dates?
  6. What Are Date and Time Intervals?
  7. How To Increment Dates by 1?
  8. How To Decrement Dates by 1?
  9. How To Calculate the Difference between Two Dates?
  10. How To Calculate the Difference between Two Time Values?
  11. How To Present a Past Time in Hours, Minutes and Seconds?
  12. How To Extract a Unit Value from a Date and Time?
  13. What Are Date and Time Functions?
  14. What Is TIMESTAMP Data Type?
  15. How Many Ways to Get the Current Time?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...