PHP/MySQL ->
Date-Time formatting functions in MySQL

1. Possible style of input params for MySQL date-time functions.

For example date 18th June 1999 can be passed in any one of the following ways: 19990618,'19990618', '1999-06-18','1999.06.18', '1999 06 18'. The following two selects returns the same result:

SELECT
   YEAR(19990618) AS the_year,
   MONTH(19990618) AS the_month,
   DAY(19990618) AS the_day;

SELECT
   YEAR('1999.06.18') AS the_year,
   MONTH('1999.06.18') AS the_month,
   DAY('1999.06.18') AS the_day;

the_yearthe_monththe_day
1999618


2. Function DATE_FORMAT()

Function DATE_FORMAT() formats the date value according to the format string.
SELECT
UPPER(CONCAT(firstname,' ', lastname)) AS Name,
DATE_FORMAT(DateOfBirth, '%a %D %b %Y') AS WasBorn_On
FROM employees
WHERE empno BETWEEN 8 AND 10;

Gives us following list:
NameWasBorn_On
DONA FIBERTue 18th May 1965
LEONARDO ROTATue 27th Nov 1979
AIVA PESHSun 10th Mar 1985


Format string of Internal date representation on the server is '%Y%m%d'. If today is 09/12/2007, internal format will return '20071209'.
There are hundreds of Format string variations that helps to shape out date representation complemeting different localites and habbits. Function GET_FORMAT() is used to return format string according to the input parameter that describes some date-format style: (INTERNAL|EUR|ISO|USA).
SELECT DATE_FORMAT(CURRENT_DATE,GET_FORMAT(DATE,'EUR')) AS EURO_Date, DATE_FORMAT(CURRENT_DATE, '%d.%m.%Y') AS '%d.%m.%Y';


SELECT DATE_FORMAT(CURRENT_DATE,GET_FORMAT(DATE,'ISO')) AS ISO_Date, DATE_FORMAT(CURRENT_DATE, '%Y-%m-%d') AS '%Y-%m-%d';


Gives the following result:
EURO_Date%d.%m.%Y
09.12.200709.12.2007

ISO_Date%Y-%m-%d
2007-12-092007-12-09


The following specifiers may be used in the format string. The % character is required before format specifier characters.

SpecifierDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th, 1st, 2nd, 3rd, )
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week
%uWeek (00..53), where Monday is the first day of the week
%VWeek (01..53), where Sunday is the first day of the week; used with %X
%vWeek (01..53), where Monday is the first day of the week; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal % character
%xx, for any x not listed above


sqlexamples.info