MySQL Quick Reference – Dates

Tested on MySQL 5.5

Date Data Types

DATE – format YYYY-MM-DD
DATETIME – format: YYYY-MM-DD HH:MM:SS
TIMESTAMP – format: YYYY-MM-DD HH:MM:SS
YEAR – format YYYY or YY

Date Functions

NOW() Returns the current date and time
CURDATE() Returns the current date
CURTIME() Returns the current time

DATE() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data in different formats

Syntax Examples:


/* Example ######################### */

CREATE TABLE Orders
(
... you want to do ...

OrderDate datetime NOT NULL DEFAULT NOW(),

OrderDate datetime NOT NULL DEFAULT CURDATE(),

OrderDate datetime NOT NULL DEFAULT CURTIME(),

... you want to do ...
)

/* Example ######################### */

SELECT ProductName, DATE(OrderDate) AS OrderDate
FROM Orders
WHERE OrderId=1

/* Example ######################### */

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay,
FROM Orders
WHERE OrderId=1

/* Example ######################### */
/* Now we want to add 45 days to the "OrderDate", to find the payment date */

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
FROM Orders

/* Example ######################### */
/* Now we want to subtract 5 days from the "OrderDate" date. */

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate
FROM Orders

/* Example ######################### */
/* The result is -1 */

SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate

/* Example ######################### */

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

/* The Result:

Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243 */