SQL Date Time – 15 Important Functions You Should Know!

Hey, folks! Hope you all are doing well. Today, in this article, we will be highlighting some of the important SQL DATE TIME functions.

What are the SQL Date Time functions?

SQL Date Time functions are a set of functions specifically defined for manipulating and dealing with datetime values. These functions help us to segregate and work with the datetime values efficiently.

So, let us get started!

1. ADDDATE() function

The ADDDATE() function enables us to add a particular expression to the specified datetime value.

Syntax:

ADDDATE(date, INTERVAL expression unit)

Thus, by using ADDDATE() function, we can add various expressions to it such as —

• second
• minute
• hour
• day
• month
• week
• year
• quarter, etc.

The ADDDATE() function returns a datetime expression after addition of the particular expression to it.

Let us try to implement the same through an example:

SELECT ADDDATE('2000-02-01', INTERVAL 5 Month) as add_month;
SELECT ADDDATE('2000-02-01', INTERVAL 25 Minute) as add_minute;

Here, we have added the month and minute expression values to the date value of the function.

Output:

add_month
2000-07-01
add_minute
2000-02-01 00:25:00

2. ADDTIME() function

The ADDTIME() function adds a datetime expression with another expression and returns the updated datetime value.

Syntax:

ADDTIME(expression1, expression2)

Consider the below example,

SELECT ADDTIME('2000-07-12 11:45:15','5 2:14:14') as updated_timestamp;

In this example, we have added day and time values to the give date time expression.

Output:

updated_timestamp
2000-07-17 13:59:29

3. CURRENT_DATE() function

The CURRENT_DATE() function extracts the current date expression from the system and returns it in the following format–

YYYY-MM-DD

Syntax:

CURRENT_DATE;

This function does not take any argument as input and returns the date expression.

SELECT CURRENT_DATE;

Output:

CURRENT_DATE
2020-07-15

4. CURRENT_TIME() function

The CURRENT_TIME() function extracts and returns the current time expression from the underlying system.

Syntax:

CURRENT_TIME;

This function returns the time expression in the form of HH–MM–SS.

Example:

SELECT CURRENT_TIME;

Output:

CURRENT_TIME
11:27:12

5. CURRENT_TIMESTAMP() function

The CURRENT_TIMESTAMP() function returns the current date-time expression from the system in the following format —

YYYY-MM-DD HH:MM:SS

Syntax:

CURRENT_TIMESTAMP;

Let us have a look at the below example to understand the working of the above function.

SELECT CURRENT_TIMESTAMP;

Output:

CURRENT_TIMESTAMP
2020-07-15T11:34:21Z

6. DATE() function

The DATE() function extracts the date expression from the passed timestamp value and returns the date.

Syntax:

DATE(timestamp);

In the below example, we have extracted the date expressions from the timestamp values passed to the function.

SELECT DATE('2020-04-04 23:34:34') as DATE;
SELECT DATE(CURRENT_TIMESTAMP) as DATE;

Output:

DATE
2020-04-04
DATE
2020-07-15

7. DATE_SUB() function

The DATE_SUB() function subtracts a particular expression from the specified date expression.

Syntax:

DATE_SUB(date, INTERVAL expression-value unit)

Let us try to implement the same through an example.

SELECT DATE_SUB('2020-12-12', INTERVAL 5 MONTH)

Output:

DATE_SUB('2020-12-12', INTERVAL 5 MONTH)
2020-07-12

8. EXTRACT() function

The EXTRACT() function extracts a particular expression from the date value and returns the expression.

Syntax:

EXTRACT(expression from date);

In the below example, we have extracted the month value from the date expression passed to the function.

SELECT EXTRACT(MONTH from '2020-5-12')

Output:

EXTRACT(MONTH from '2020-5-12')
5

9. NOW() function

The NOW() function returns the current timestamp from the system.

Syntax:

NOW();

Example:

SELECT NOW() as Present_Time;

Output:

Present_Time
2020-07-15T12:17:08Z

10. QUARTER() function

The QUARTER() function returns the quarter value of the year from the date expression passed to it.

Syntax:

SELECT QUARTER(NOW()) as Quarter_Year;

Here the NOW() function returns 2020-07-15T12:17:08Z as the timestamp value from which the quarter value based upon the month as described below–

• 1-3: 1st Quarter
• 4-6: 2nd Quarter
• 7-9: 3rd Quarter
• 10-12: 4th Quarter

Output:

Quarter_Year
3

11. TIME() function

The Time() function extracts the time expression from the datetime expression and returns the time value.

Syntax:

TIME(datetime)

Example:

SELECT TIME(NOW());

Output:

TIME(NOW())
07:34:38

12. TIMESTAMP() function

The TIMESTAMP() function takes date expression as an argument and adds the time expression to it. Thus, the function returns a datetime expression.

Syntax:

TIMESTAMP(date)

Example:

SELECT TIMESTAMP('2020-11-11');

Output:

TIMESTAMP('2020-11-11')
2020-11-11T00:00:00Z

13. TIMEDIFF() function

The TIMEDIFF() function returns the difference between two-time expressions.

Syntax:

TIMEDIFF(time1, time2)

Example:

SELECT TIMEDIFF('30:12:20','20:10:10');

Output:

TIMEDIFF('30:12:20','20:10:10')
10:02:10

14. TO_DAYS() function

The TO_DAYS() function returns the count of days between 0 and the date expression passed to the function.

Syntax:

TO_DAYS(date)

Example:

SELECT TO_DAYS('2020-10-10')

Output:

TO_DAYS('2020-10-10')
738073

15. Extracting portion of datetime expressions

We can extract the year, month, hour, seconds value, etc from the datetime expressions as shown below–

SELECT YEAR('2020-10-10 12:30:30') as Year;
SELECT MONTH('2020-10-10 12:30:30') as Month;
SELECT MINUTE('2020-10-10 12:30:30') as Minute;
SELECT WEEK('2020-10-10 12:30:30') as WEEK;

Output:

Year
2020
Month
10
Minute
30
WEEK
40

Conclusion

By this, we have come to the end of this topic. Feel free to comment below in case you come across any question.

For more such posts related to SQL, do visit SQL JournalDev.

References

• SQL DATE and TIME functions — Documentation