Broad Network


Parts of MySQL Date and Time

Date and Time in MySQL – Part 3

Foreword: 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: 13 Apr 2016

Introduction

This is part 3 of my series, Date and Time in MySQL. 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. You should have read the previous parts of the series, because this is a continuation. All the functions in this part of the series, take an argument of date and/or time or datetime type.

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

The MONTHNAME() Function
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

Related Links

Implementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
MySQL Transaction
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course

BACK NEXT

Comments

Become the Writer's Fan
Send the Writer a Message