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 */