Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Date and Time Functions in DBMS
The date and time functions in DBMS are quite useful to manipulate and store values related to date and time.
The different date and time functions are as follows −
ADDDATE(DATE,DAYS)
The numbers of days in integer form (DAYS) is added to the specified date. This is the value returned by the function. For example −
sql> SELECT ADDDATE('2018-08-01', 31);
+---------------------------------------------------------+
| DATE_ADD('2018-08-01', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 2018-09-01 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This function adds 31 days to the given date i.e. ‘2018-08-01’ and returns the resultant date i.e. ‘2018-09-01’.
ADDTIME(exp1,exp2)
This function adds the two expressions exp1 and exp2 and displays the result. In this case, exp1 can be a datetime or time expression and exp2 is a time expression. For example:
sql> SELECT ADDTIME('2018-08-01 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
|ADDTIME('2018-08-01 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 2018-08-02 01:01:01.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)The time '1 1:1:1.000002' is added to the datetime function '2018-08-01 23:59:59.999999' to give the resultant value ‘2018-08-02 01:01:01.000001’
CURDATE()
This returns the current date of the system in the YYYY-MM-DD format. For example −
sql> SELECT CURDATE(); +---------------------------------------------------------+ | CURDATE() | +---------------------------------------------------------+ | 2018-08-01 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
This function returns the current date i.e. ‘2018-08-01’
CURTIME()
This returns the current time of the system from the current time zone in the format HH:MM:SS. For example −
sql> SELECT CURTIME(); +---------------------------------------------------------+ | CURTIME() | +---------------------------------------------------------+ | 10:56:35 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
This function returns the current time i.e ‘10:56:35’
DAYNAME(date)
For the given date, this function returns the corresponding day of the week. For example −
sql> SELECT DAYNAME('2018-08-01');
+---------------------------------------------------------+
| DAYNAME('2018-08-01') |
+---------------------------------------------------------+
| Wednesday |
+---------------------------------------------------------+
1 row in set (0.00 sec)For the date '2018-08-01’, this function returns the day of the week i.e. Wednesday.
DAYOFMONTH(date)
For the given date, it returns the day of the month the date is on. The value of day of the month ranges from 1 to 31. For example −
sql> SELECT DAYOFMONTH('2018-02-15');
+---------------------------------------------------------+
| DAYOFMONTH('2018-02-15') |
+---------------------------------------------------------+
| 15 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This returns the day of the month '2018-02-15' falls on i.e 15.
DAYOFWEEK(date)
For the given date, it returns the day of the week the date is on. The value of day of the week ranges from 1 to 7 (Sunday is 1 and Saturday is 7). For example −
sql> SELECT DAYOFWEEK('2018-02-15');
+---------------------------------------------------------+
|DAYOFWEEK('2018-02-15') |
+---------------------------------------------------------+
| 5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This returns the day of the week '2018-02-15' falls on i.e 5.
DAYOFYEAR(date)
For the given date, it returns the day of the year the date is on. The value of day of the year ranges from 1 to 366. For example −
sql> SELECT DAYOFYEAR('2018-02-15');
+---------------------------------------------------------+
| DAYOFYEAR('2018-02-15') |
+---------------------------------------------------------+
| 46 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This returns the day of the year '2018-02-15' falls on i.e 46.
MONTH(date)
It returns the month value for the corresponding date. The range of the month is from 1 to 12. For example −
sql> SELECT MONTH('2018-08-01');
+---------------------------------------------------------+
| MONTH('2018-08-01') |
+---------------------------------------------------------+
| 8 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This returns the month number for '2018-08-01' which is 8.
TIME(expr)
This function displays the time part of a time or date time expression in the form of a string. For example −
sql> SELECT TIME('2018-08-01 11:33:25');
+---------------------------------------------------------+
| TIME('2018-08-01 11:33:25') |
+---------------------------------------------------------+
| 11:33:25 |
+---------------------------------------------------------+
1 row in set (0.00 sec)This displays the time part of '2018-08-01 11:33:25' in the form of a string.
4K+ Views
