SQL  DATEDIFF() function returns the time between two dates.

Table – orders

+—–+—–+——-+———————+———————+
| oid | uid | items | date                | delivery_date       |
+—–+—–+——-+———————+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 | 2007-05-24 00:00:00 |
+—–+—–+——-+———————+———————+

Example

mysql> SELECT DATEDIFF(delivery_date,date) AS days FROM orders;
+——+
| days |
+——+
|    8 |
+——+
1 row in set (0.03 sec)

SQL DATE_SUB() function subtracts a specified time interval from a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_SUB(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_SUB(date, INTERVAL 4 DAY) |
+——————————–+
| 20007-05-12 00:00:00            |
+——————————–+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL DATE_ADD() function adds a specified time interval to a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_ADD(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_ADD(date, INTERVAL 4 DAY) |
+——————————–+
| 2007-05-20 00:00:00            |
+——————————–+
1 row in set (0.01 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL EXTRACT() return a single part of a date/time(likes year, month, day, hour, minute)

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT EXTRACT(YEAR FROM date) FROM orders;
+————————-+
| EXTRACT(YEAR FROM date) |
+————————-+
|                    2007 |
+————————-+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL DATE() function extracts the date part of a date.

Table  – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE(date) as bill_date from orders ;
+————+
| bill_date  |
+————+
| 2007-05-16 |
+————+
1 row in set (0.00 sec)

SQL CURTIME() returns the current time.

Example

mysql> SELECT CURTIME();
+———–+
| CURTIME() |
+———–+
| 15:28:57  |
+———–+
1 row in set (0.01 sec)

SQL CURDATE() returns the current date.

Example

mysql> SELECT CURDATE();
+————+
| CURDATE()  |
+————+
| 2007-05-30 |
+————+
1 row in set (0.00 sec)

SQL NOW() returns the current date and time.

Example

mysql> SELECT NOW();
+———————+
| NOW()               |
+———————+
| 2007-05-30 14:47:17 |
+———————+
1 row in set (0.04 sec)

SQL support many functions with which we can manage the dates efectively

Below given are the date functions is MySql

• NOW()- Returns the current date and time
• CURDATE()- Returns the current date
• CURTIME()- Returns the current time
• DATE()- Extracts the date part of a date or date/time expression
• EXTRACT()- Returns a single part of a date/time
• DATE_ADD()- Adds a specified time interval to a date
• DATE_SUB() -Subtracts a specified time interval from a date
• DATEDIFF()- Returns the number of days between two dates
• DATE_FORMAT()- Displays date/time data in different formats