Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Formatting dates in MySQL server query (http://www.go4expert.com/articles/formatting-dates-mysql-server-query-t1994/)

pradeep 25Nov2006 14:43

Formatting dates in MySQL server query
 

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



All times are GMT +5.5. The time now is 06:17.