9 really useful MySQL date functions that are easy to remember

There are some really useful MySQL date functions that help you directly get the required part of a date. Here are the commonly used MySQL date functions. These MySQL date functions are very easy to remember.

MySQL date functions syntax:

function_name(date)

function_name – it can be any of the ones below like year, month, etc.

date – date string in the format ‘YYYY-MM-DD HH:MM:SS’. It can be a date or datetime column from a table, string or system function like NOW()

To directly calculate Year from date or datetime, you can use MySQL Year function:

#using system function now()
SELECT YEAR(NOW());

#using a string
SELECT YEAR('2011-11-05 11:45:00');

#using a date column
CREATE TABLE TEST(DT DATETIME);
INSERT INTO TEST(DT) VALUES('2010-10-05 10:45:00');
SELECT YEAR(DT) from TEST;

Result:

2013
2013
2010

The NOW() function shows you the current date and time. I have shown the latest date and time here for reference.

SELECT NOW();
'2013-12-05 10:37:46'

To directly calculate Month from date or datetime, you can use MySQL Month function:

SELECT MONTH(NOW());
12

To directly calculate Date from datetime, you can use MySQL Date function:

SELECT DATE(NOW()); 
2013-12-05

To directly calculate Day from date or datetime, you can use MySQL Day function:

SELECT DAY(NOW()); 
5

To directly calculate Week number from date or datetime, you can use MySQL Week function:

SELECT WEEK(NOW()); 
48

To directly calculate Year & Week from date or datetime, you can use MySQL YearWeek function:

SELECT YEARWEEK(NOW()); 
12

To directly calculate Hours from time or datetime, you can use MySQL Hour function:

SELECT HOUR(NOW()); 
10

For date field the time is returned as ’00:00:00′. So you will see the result as 0.

#using a date column
CREATE TABLE TEST(DT DATE);
INSERT INTO TEST(DT) VALUES('2010-10-05');
SELECT HOUR(DT) from TEST;
0

To directly calculate Minutes from time or datetime, you can use MySQL Minute function:

SELECT MINUTE(NOW()); 
37

For date field the time is returned as ’00:00:00′. So you will see the result as 0.
To directly calculate Seconds from time or datetime, you can use MySQL Second function:

SELECT SECOND(NOW());
46

For date field the time is returned as ’00:00:00′. So you will see the result as 0.