Parts of MySQL Date and Time
Date and Time in MySQL – Part 3
Forward: In this part of the series, I explain the parts of MySQL date and time which are the second, minute, hour, day, week, month, and year.
By: Chrysanthus Date Published: 4 Sep 2012
Introduction
The SECOND Function
The second function takes the argument of time and returns the second part of the time. The syntax is:
SECOND(time)
The time value can be of type datetime or simply time. The following two statements illustrate this:
SELECT second('2008-04-03 10:06:03');
SELECT second('10:06:03');
The return value for the two statements is, 3.
The MINUTE() Function
The minute function takes the argument of time and returns the minute part of the time. The syntax is:
MINUTE(time)
The time value can be of type datetime or simply time. The following two statements illustrate this:
SELECT minute('2008-04-03 10:06:03');
SELECT minute('10:06:03');
The return value for the two statements is, 6.
The HOUR() Function
The hour function takes the argument of time and returns the hour part of the time. The syntax is:
hour(time)
The time value can be of type datetime or simply time. The following two statements illustrate this:
SELECT hour('2008-04-03 10:06:03');
SELECT hour('10:06:03');
The return value for the two statements is, 10.
The DAYOFMONTH function takes the argument of date and returns the day-of-month part of the date. The syntax is:
DAYOFMONTH(date)
The date value can be of type datetime or simply date. The following two statements illustrate this:
SELECT dayofmonth('2008-04-05 10:06:03');
SELECT dayofmonth('2008-04-05');
The return value for the two statements is, 5.
The MONTH() Function
The MONTH() function takes the argument of date and returns the month of the date as a number in the range 1 to 12. The syntax is:
MONTH(date)
The date value can be of type datetime or simply date. The following two statements illustrate this:
SELECT month('2008-04-05 10:06:03');
SELECT month('2008-04-05');
The return value for the two statements is, 4.
The YEAR() Function
The YEAR() function takes the argument of date and returns the year of the date as a four digit number. The syntax is:
YEAR(date)
The date value can be of type datetime or simply date. The following two statements illustrate this:
SELECT year('2008-04-05 10:06:03');
SELECT year('2008-04-05');
The return value for the two statements is, 2008. Avoid using arguments with 2-digit numbers.
Names of the Month and Day of the Week
Note from the above illustrations that it is the month number that was return for the MONTH() Function. It is possible to return the name of the month such as January or February or March, … or December. To achieve this, you need but the MONTHNAME() Function. The MONTHNAME() function takes the argument of date and returns the name of the month of the date. The syntax is:
MONTHNAME(date)
The date value can be of type datetime or simply date. The following two statements illustrate this:
SELECT monthname('2008-04-05 10:06:03');
SELECT monthname('2008-04-05');
The return name (value) for the two statements is, April.
The DAYNAME() Function
You can determine the name of the day of the week from the date. That is, you can know if the date is for a Sunday or Monday or Tuesday … or Saturday from the date. In this case you use the DAYNAME() Function whose syntax is:
DAYNAME(date)
The date value can be of type datetime or simply date. The following two statements illustrate this:
SELECT dayname('2014-06-05 10:06:03');
SELECT dayname('2014-06-05');
The return name for the two statements is, Thursday.
That is it for this part of the series. We stop here and continue in the next part.
Chrys