MySQL Programming Question:
Download Questions PDF

How To Convert Dates to Character Strings?

Answers:

Answer #1
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)

Answer #2
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


Download MySQL Programming Interview Questions And Answers PDF

Previous QuestionNext Question
How To Enter Microseconds in SQL Statements?How To Convert Character Strings to Dates?