Formatting dates in MySQL server query

Discussion in 'MySQL' started by pradeep, Nov 25, 2006.

  1. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,645
    Likes Received:
    87
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    http://blog.pradeep.net.in

    Introduction



    Sometimes we need to get a formatted date while getting the date from a MySQL database using a SQL query. Fortunately, MySQL has an in-built function called DATE_FORMAT which will help us to acheive the same.

    Code:
    DATE_FORMAT(date,format)

    What is what??



    Here are the format specifiers and their description:
    Code:
    Specifier	Description
     =============================
     %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)		
     %U		Week (00..53), where Sunday is the first day of the week		
     %u		Week (00..53), where Monday is the first day of the week		
     %V		Week (01..53), where Sunday is the first day of the week; used with %X		
     %v		Week (01..53), where Monday is the first day of the week; used with %x		
     %W		Weekday name (Sunday..Saturday)		
     %w		Day of the week (0=Sunday..6=Saturday)		
     %X		Year for the week where Sunday is the first day of the week, numeric, 4 digits; used with %V		
     %x		Year for the week, where Monday is the first day of the week, numeric, 4 digits; used with %v		
     %Y		Year, numeric, 4 digits		
     %y		Year, numeric, 2 digits		
     %%		A literal `%'.   

    Example usage:

    Code:
    SELECT DATE_FORMAT(doj,'%D of %M, %Y') FROM em_table;
     // will return
     26th of June, 1978
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice