SQL Date Function

Introduction to SQL Date Function

Working with the database is not very difficult as one needs to know the basic queries and keywords that should be used to retrieve, update and delete the data in the database. SQL allows us to store the structured data (data in the form of rows and columns) and provides simple queries to work on it. In this topic, we are going to discuss the SQL Date Function.

The date is one of the most important functions used in the SQL, but it is somewhat difficult for newbies to understand it as there are many formats in which date can be stored in the database and in different formats the user wants to retrieve it depending on the specific requirements. In SQL, DateTime (time is also used along with the date) is frequently used to store both the date and time values at a time in a single column.

Date Functions of SQL

Let’s understand each date function used in SQL one by one in detail:

1. NOW()​

NOW() is used to return the current system date and time value.

Query​:

SELECT NOW();

Output:​ 2019-08-06 08:10:12

2. CURDATE()

CURDATE() is used to return the current system date.

Query​:

SELECT CURDATE();

Output:​ 2019-08-06

3. CURTIME()

CURTIME() is used to return the current system time.

Query:​

SELECT CURTIME();

Output:​ 08:10:12

4. DATE(expr)

Extracts only the date part from the column having the DateTime value.

Query​:

SELECT DATE(‘2019-08-06 08:10:12’);

Output​: 2019-08-06

5. EXTRACT(unit FROM DATE)

EXTRACT() function is used to retrieve a particular part of the date/time value.

Consider the following table ‘student’ for this:

id	name	birthday
1001	Ankita	2019-08-06 08:10:12

Query​:

SELECT id, name, EXTRACT(DAY FRO from the student; M birthday) AS day_of_birth

id	name	day_of_birth
1001	Ankita	08

6. DATEADD(date, INTERVAL expr unit):​

This function is used in the scenarios when we want to display date by adding some value to the original value.6. DATEADD(date, INTERVAL expr unit)

Query​:

SELECT DATEADD(‘2019-06-05’, INTERVAL 5 DAY);

Output​: 2019-06-10

Here Unit which is DAY in the above example, can have different values :

-MICROSECOND

-SECOND-DAY

-HOUR-WEEK

-YEAR-DAY

-MONTH-DAY

-HOUR-DAY

-SECOND-QUARTER

-MINUTE_SECOND

-HOUR_SECOND

7. DATEDIFF()

This function returns the difference of the number of days from date2 to date1.

​Query:​

SELECT DATEDIFF(‘2019-06-03’ , ‘2019-06-05’);

Output:​ 2

​8. DATE_FORMAT(date, format)

This is one of the important functions used in SQL.

This function returns the date in various formats depending on the specifier mentioned by the user.

Specifier	Description
%a	DIsplays abbreviated weekday name (Sun-Sat)
%D	Displays day of the month with English suffix (1st, 2nd, 3rd..)
%d	Displays day of the month in numeric values (00..31)
%b	DIsplays abbreviated month name (Jan-Dec)
%c	Displays month in numeric values (1, 2, 3…)
%h	Displays hour (01-12)
%H	Displays hour (00-23)
%m	Displays month name in numeric (0-12)
%M	Displays month name in English language (January- December)
%w	Displays day of the week (0-6)
%W	Displays weekday name (Sunday-Saturday)
%s	Displays seconds (00..59)
%S	Displays seconds (00..59)
%y	Displays the numeric year in two digits
%Y	Displays the numeric year in four digits

Query​:

SELECT DATE_FORMAT(‘2019-06-03 09:40:05’, %W %M %y);

Output:​ Monday, June 19

9​. DAYNAME(date)

This function returns the name of the date for the date specified.​

Query​:

DAYNAME(‘2019-06-03’);

Output​: Monday

​10. DAYOFMONTH(date)

This function returns the day of the month for the date specified.

Query:​

DAYOFMONTH(‘2019-06-03’);

Output:​ 3

11. DAYOFWEEK(date)

This function returns the weekday index of the date given.​ Indexes assigned are Sunday=1, Monday=2, Tuesday=3, and so on.

Query​:

DAYOFWEEK(‘2019-06-03’);

Output​: 2

12. DAYOFYEAR(date)

​This function returns the day of the month for a year for the date specified. Ranges from 1 to 366.

Query​:

DAYOFYEAR(‘2019-06-03’);

Output​: 154

13. DATE_SUB(date, INTERVAL expr unit)

This function is used to subtract the given​ date/time values from the date specified.

Query:

SELECT DATE_SUB(‘2019-06-03’, INTERVAL 4 DAY);

Output​: 2019-05-31

Like the DATE_ADD() function, there can be various INTERVAL values.

14. MAKEDATE(year, days)

This function creates a date based on the year and the days provided by the user. But the year provided should be of 4 digits.

Query:

SELECT MAKEDATE(2019,154);

Output: 2019-06-03

15. MAKETIME(hour, minute, second)

This function returns the time based on the hour, minute and second value provided by the user.

Query​:

SELECT MAKETIME(09,20,05);

Output​: 09:20:05

Other Date Functions

S.N o.	Function Name	Description	Example	Output
1.	LAST_DAY(date)	Takes the date or DateTime value and returns the last day of the corresponding month	SELECT LAST_DAY(‘2019-06-09’)	2019-06-30
2.	FROM_DAYS(n)	Takes n number of days as input and returns the date value	SELECT FROM_DAYS(685552)	1876-12-23
3.	HOUR(time)	Takes the time value and returns the hour value	SELECT HOUR(‘03:24:27’)	03
4.	MINUTE(time)	Takes the time values as input and returns the minutes value	SELECT MINUTE(‘03:27:24’)	27
5.	MONTH(date)	Takes the date as input and returns the corresponding month of the date given	SELECT MONTH(‘2019-06-09’)	06
6.	SYSDATE()	Returns the current date and time value in the format YYYY MM DD HH:MM:SS	SELECT SYSDATE()	2019-06-09 11:15:08
7.	TIME_TO_SEC(time)	Take time as input and returns total seconds corresponding to that time	SELECT TIME_TO_SEC(‘11:15 :10’)	40510
8.	YEAR(date)	Take date as input and returns the year for the corresponding date	SELECT YEAR (‘19-06-09’)	2019
		in the range of 1000-9999
9.	TIMESTAMP(expr)	Returns the date or datetime expression	SELECT TIMESTAMP (‘2019-06-09’)	2019-06-09 00:00:00
10.	WEEKDAY(date)	Take date as input and returns the weekday index corresponding to that date (0= Monday, 1= Tuesday, 2= Wednesday, so on)	SELECT WEEKDAY (‘2019-06-09’)	6

Conclusion

The above mentioned are some of the basic date functions used in SQL. There are several other date functions used in different scenarios. One must need to keep in mind the syntax and the argument passed in the function before using any of them to avoid unexpected results.

Recommended Articles

This is a guide to SQL Date Function. Here we discuss the Date Function used in SQL one by one with Examples and Outputs in detail. You may also have a look at the following articles to learn more –

1. Foreign Key in SQL
2. SQL Insert Query
3. Types of Joins in SQL
4. JavaScript Date Function