MySQL Date Functions

CURDATE

Returns the current date.

SELECT CURDATE();
SELECT CURDATE() + 0;
SELECT CURRENT_DATE(), CURRENT_DATE, CURDATE();
SELECT DATE(NOW());

DATEDIFF

Calculates the number of days between two DATE values.

SELECT DATEDIFF('2011-08-17','2011-08-17'); --  0 day
SELECT DATEDIFF('2011-08-17','2011-08-08'); --  9 days
SELECT DATEDIFF('2011-08-08','2011-08-17'); -- -9 days
SELECT orderNumber, DATEDIFF(requiredDate, shippedDate) daysLeft FROM orders ORDER BY daysLeft DESC;
SELECT orderNumber, DATEDIFF(requiredDate, orderDate) remaining_days FROM orders WHERE status = 'In Process' ORDER BY remaining_days;
SELECT orderNumber, ROUND(DATEDIFF(requiredDate, orderDate) / 7, 2), ROUND(DATEDIFF(requiredDate, orderDate) / 30,2) FROM orders WHERE status = 'In Process';

DAY

Gets the day of the month of a specified date.

SELECT DAY('2010-01-15');
SELECT DAY(LAST_DAY('2016-02-03'));
SELECT DAY(orderdate) dayofmonth, COUNT(*) FROM orders WHERE YEAR(orderdate) = 2004 GROUP BY dayofmonth ORDER BY dayofmonth;

DATE_ADD

Adds a time value to date value.

SELECT DATE_ADD('1999-12-31 23:59:59', INTERVAL 1 SECOND) result;
SELECT DATE_ADD('1999-12-31 00:00:01', INTERVAL 1 DAY) result;
SELECT DATE_ADD('1999-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) result;
SELECT DATE_ADD('2000-01-01 00:00:00', INTERVAL '-1 5' DAY_HOUR) result;
SELECT DATE_ADD('1999-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) result;
SELECT DATE_ADD('2000-01-01', INTERVAL 5 / 2 HOUR_MINUTE) result;
SELECT DATE_ADD('2000-01-01', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE) result;
SELECT DATE_ADD('2000-01-01', INTERVAL 12 HOUR) result;
SELECT DATE_ADD('2000-02-30', INTERVAL 1 DAY) result;
SHOW WARNINGS;
SELECT DATE_ADD('2010-01-30', INTERVAL 1 MONTH) result;
SELECT DATE_ADD('2012-01-30', INTERVAL 1 MONTH) result;

DATE_SUB

Subtracts a time value from a date value.

SELECT DATE_SUB('2017-07-04',INTERVAL 1 DAY) result;
SELECT DATE_SUB('2017-07-04',INTERVAL 3 HOUR) result;
SELECT DATE_SUB('2017-07-03',INTERVAL -1 DAY) result;
SELECT DATE_SUB('2017-02-29', INTERVAL - 1 DAY) result;
SHOW WARNINGS;
SELECT DATE_SUB('03/07/2017', INTERVAL 1 DAY) result;
SELECT DATE_SUB(NULL, INTERVAL 1 DAY) result;
SELECT DATE_SUB('2017-03-30', INTERVAL 1 MONTH) result;

DATE_FORMAT

Formats a date value based on a specified date format.

Specifier Meaning
%a Three-characters abbreviated weekday name e.g., Mon, Tue, Wed, etc.
%b Three-characters abbreviated month name e.g., Jan, Feb, Mar, etc.
%c Month in numeric e.g., 1, 2, 3…12
%D Day of the month with English suffix e.g., 0th, 1st, 2nd, etc.
%d Day of the month with leading zero if it is 1 number e.g., 00, 01,02, …31
%e Day of the month without leading zero e.g., 1,2,…31
%f Microseconds in the range of 000000..999999
%H Hour in 24-hour format with leading zero e.g., 00..23
%h Hour in 12-hour format with leading zero e.g., 01, 02…12
%I Same as %h
%i Minutes with leading zero e.g., 00, 01,…59
%j Day of year with leading zero e.g., 001,002,…366
%k Hour in 24-hour format without leading zero e.g., 0,1,2…23
%l Hour in 12-hour format without leading zero e.g., 1,2…12
%M Full month name e.g., January, February,…December
%m Month name with leading zero e.g., 00,01,02,…12
%p AM or PM, depending on other time specifiers
%r Time in 12-hour format hh:mm:ss AM or PM
%S Seconds with leading zero 00,01,…59
%s Same as %S
%T Time in 24-hour format hh:mm:ss
%U Week number with leading zero when the first day of week is Sunday e.g., 00,01,02…53
%u Week number with leading zero when the first day of week is Monday e.g., 00,01,02…53
%V Same as %U; it is used with %X
%v Same as %u; it is used with %x
%W Full name of weekday e.g., Sunday, Monday,…, Saturday
%w Weekday in number (0=Sunday, 1= Monday,etc.)
%X Year for the week in four digits where the first day of the week is Sunday; often used with %V
%x Year for the week, where the first day of the week is Monday, four digits; used with %v
%Y Four digits year e.g., 2000 and 2001.
%y Two digits year e.g., 10,11,and 12.
%% Add percentage (%) character to the output
SELECT orderNumber,DATE_FORMAT(orderdate, '%Y-%m-%d') orderDate,DATE_FORMAT(requireddate, '%a %D %b %Y') requireddate,DATE_FORMAT(shippedDate, '%W %D %M %Y') shippedDate FROM orders;
SELECT orderNumber,DATE_FORMAT(shippeddate, '%W %D %M %Y') shippeddate FROM orders WHERE shippeddate IS NOT NULL ORDER BY shippeddate;
SELECT orderNumber,DATE_FORMAT(shippeddate, '%W %D %M %Y') 'Shipped date'FROM orders WHERE shippeddate IS NOT NULL ORDER BY shippeddate;

DAYNAME

Gets the name of a weekday for a specified date.

SELECT DAYNAME('2000-01-01') dayname;
SELECT @@lc_time_names;
SET @@lc_time_names = 'zh_CN';
SELECT DAYNAME(orderdate) weekday, COUNT(*) total_orders FROM orders WHERE YEAR(orderdate) = 2004 GROUP BY weekday ORDER BY total_orders DESC;

DAYOFWEEK

Returns the weekday index for a date.

SELECT DAYNAME('2012-12-01'), DAYOFWEEK('2012-12-01');

EXTRACT

Extracts part of a date.

SELECT EXTRACT(DAY FROM '2017-07-14 09:04:44') DAY;
SELECT EXTRACT(DAY_HOUR FROM '2017-07-14 09:04:44') DAYHOUR;
SELECT EXTRACT(DAY_MICROSECOND FROM '2017-07-14 09:04:44') DAY_MS;
SELECT EXTRACT(DAY_MINUTE FROM '2017-07-14 09:04:44') DAY_M;
SELECT EXTRACT(DAY_SECOND FROM '2017-07-14 09:04:44') DAY_S;
SELECT EXTRACT(HOUR FROM '2017-07-14 09:04:44') HOUR;
SELECT EXTRACT(HOUR_MICROSECOND FROM '2017-07-14 09:04:44') HOUR_MS;
SELECT EXTRACT(HOUR_MINUTE FROM '2017-07-14 09:04:44') HOUR_M;
SELECT EXTRACT(HOUR_SECOND FROM '2017-07-14 09:04:44') HOUR_S;
SELECT EXTRACT(MICROSECOND FROM '2017-07-14 09:04:44') MICROSECOND;
SELECT EXTRACT(MINUTE FROM '2017-07-14 09:04:44') MINUTE;
SELECT EXTRACT(MINUTE_MICROSECOND FROM '2017-07-14 09:04:44') MINUTE_MS;
SELECT EXTRACT(MINUTE_SECOND FROM '2017-07-14 09:04:44') MINUTE_S;
SELECT EXTRACT(MONTH FROM '2017-07-14 09:04:44') MONTH;
SELECT EXTRACT(QUARTER FROM '2017-07-14 09:04:44') QUARTER;
SELECT EXTRACT(SECOND FROM '2017-07-14 09:04:44') SECOND;
SELECT EXTRACT(SECOND_MICROSECOND FROM '2017-07-14 09:04:44') SECOND_MS;
SELECT EXTRACT(WEEK FROM '2017-07-14 09:04:44') WEEK;
SELECT EXTRACT(YEAR FROM '2017-07-14 09:04:44') YEAR;
SELECT EXTRACT(YEAR_MONTH FROM '2017-07-14 09:04:44') YEARMONTH;

NOW

Returns the current date and time at which the statement executed.

SELECT NOW();
SELECT NOW() + 0;
SELECT NOW(), SLEEP(5), NOW();
SELECT SYSDATE(), SLEEP(5), SYSDATE();
       -- mysql now minus 1 hour
SELECT (NOW() - INTERVAL 1 HOUR) 'NOW - 1 hour',
        NOW(),
       -- mysql now plus 1 hour
       NOW() + INTERVAL 1 HOUR 'NOW + 1 hour';
        -- mysql now minus 1 day
SELECT (NOW() - INTERVAL 1 DAY) 'NOW - 1 day',
        NOW(),
        -- mysql now plus 1 day
        (NOW() + INTERVAL 1 DAY) 'NOW + 1 day';
CREATE TABLE tmp(
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    created_on DATETIME NOT NULL DEFAULT NOW() -- or CURRENT_TIMESTAMP
);
INSERT INTO tmp(title) VALUES('Test NOW() function');
SELECT * FROM tmp;

MONTH

Returns an integer that represents a month of a specified date.

SELECT MONTH('2010-01-01');
SELECT MONTH(NOW()) CURRENT_MONTH;
SELECT MONTH('0000-00-00');
SELECT MONTH(NULL);
SELECT MONTH(orderDate) month,ROUND(SUM(quantityOrdered * priceEach)) subtotal FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE YEAR(orderDate) = 2004 GROUP BY month;

STR_TO_DATE

Converts a string into a date and time value based on a specified format.

SELECT STR_TO_DATE('21,5,2013','%d,%m,%Y');
SELECT STR_TO_DATE('21,5,2013 extra characters','%d,%m,%Y');
SELECT STR_TO_DATE('2013','%Y');
SELECT STR_TO_DATE('113005','%h%i%s');
SELECT STR_TO_DATE('11','%h');
SELECT STR_TO_DATE('20130101 1130','%Y%m%d %h%i');

SYSDATE

Returns the current date.

SELECT SYSDATE();
SELECT SYSDATE(3);
SELECT SYSDATE(), NOW();

TIMEDIFF

Calculates the difference between two TIME or DATETIME values.

SELECT TIMEDIFF('12:00:00','10:00:00') diff;
SELECT TIMEDIFF('2010-01-01 01:00:00','2010-01-02 01:00:00') diff;
SELECT TIMEDIFF('2010-01-01',NULL) diff;
SELECT TIMEDIFF('2010-01-01 10:00:00','10:00:00') diff;
SELECT TIMEDIFF('2009-03-01 00:00:00','2009-01-01 00:00:00') diff;
SHOW WARNINGS;
SELECT TIMESTAMPDIFF(HOUR,'2009-01-01 00:00:00','2009-03-01 00:00:00') diff;

TIMESTAMPDIFF

Calculates the difference between two DATE or DATETIME values.

SELECT TIMESTAMPDIFF(MONTH, '2010-01-01', '2010-06-01') result;
SELECT TIMESTAMPDIFF(DAY, '2010-01-01', '2010-06-01') result;
SELECT TIMESTAMPDIFF(MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:00') result;
SELECT TIMESTAMPDIFF(MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:59') result;
SELECT TIMESTAMPDIFF(SECOND, '2010-01-01 10:00:00', '2010-01-01 10:45:59') result;
CREATE TABLE persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL
);
INSERT INTO persons(full_name, date_of_birth)
VALUES('John Doe', '1990-01-01'),
      ('David Taylor', '1989-06-06'),
      ('Peter Drucker', '1985-03-02'),
      ('Lily Smith', '1992-05-05'),
      ('Mary William', '1995-12-01');
SELECT id,full_name,date_of_birth,TIMESTAMPDIFF(YEAR,date_of_birth,'2017-01-01') age FROM persons;
SELECT id,full_name,date_of_birth,TIMESTAMPDIFF(YEAR,date_of_birth,NOW()) age FROM persons;

WEEK

Returns a week number of a date.

Mode First day of week Range Week 1 is the first week …
0 Sunday 0-53 with a Sunday in this year
1 Monday 0-53 with 4 or more days this year
2 Sunday 1-53 with a Sunday in this year
3 Monday 1-53 with 4 or more days this year
4 Sunday 0-53 with 4 or more days this year
5 Monday 0-53 with a Monday in this year
6 Sunday 1-53 with 4 or more days this year
7 Monday 1-53 with a Monday in this year
SHOW VARIABLES LIKE 'default_week_format';
SELECT WEEK(orderDate) week_no, COUNT(*) FROM orders WHERE YEAR(orderDate) = 2003 GROUP BY WEEK(orderDate);

WEEKDAY

Returns a weekday index for a date.

SELECT DAYNAME('2010-01-01'), WEEKDAY('2010-01-01');