SQL Date Functions
In this tutorial, we will learn about some of the important date functions which are in-built in SQL.
Date functions in SQL:
- NOW()
- CURDATE()
- CURTIME()
- DATE()
- EXTRACT()
- DATE_ADD()
- DATE_SUB()
- DATEDIFF()
- DATE_FORMAT()
Let us see each of the date functions one by one in detail with the help of examples. We will be using MySQL database in examples for writing the queries.
Consider we have the student table with the following data:
| ID | Name | DateTime_Birth | City |
|---|---|---|---|
| 1 | Mansi Shah | 2010-01-01 18:39:09 | Pune |
| 2 | Tejal Wagh | 2010-03-04 05:13:19 | Nasik |
| 3 | Sejal Kumari | 2010-05-01 10:31:07 | Mumbai |
| 4 | Sonal Jain | 2010-09-09 17:17:07 | Shimla |
| 5 | Surili Maheshwari | 2010-07-10 20:45:18 | Surat |
We will be using this table in some of the examples.
1. NOW():
NOW () function in SQL will give the current system's date and time.
Syntax:
Example:
Write a query to display the current system's date and time.
Query:
Here, we have written a SELECT query with the NOW () function to get the system's current date and time. Current_Date_Time is an alias to store the date and time.
After executing the above query, we got the following output:
| Current_Date_Time |
|---|
| 2021-10-24 18:28:44 |
The date and time at which the query was executed in the system are 24th October 2021 and 18:28:44. Hence, it is displayed as an output.
2. CURDATE()
CURDATE () function in SQL will give the current system's date.
Syntax:
Example:
Write a query to display the current system's date.
Query:
Here, we have written a SELECT query with the CURDATE () function to get the system's current date. CurrentDate is an alias to store the date.
After executing the above query, we got the following output:
| CurrentDate |
|---|
| 2021-10-24 |
The date on which the query was executed in the system is 24th October 2021. Hence, it is displayed as an output.
3. CURTIME()
CURTIME () function in SQL will give the current system time.
Syntax:
Example:
Write a query to display the current system's time.
Query:
Here, we have written a SELECT query with the CURTIME () function to get the system's current time. CurrentTime is an alias to store the time.
After executing the above query, we got the following output:
| CurrentTime |
|---|
| 18:49:07 |
The time at which the query was executed in the system is 18:49:07. Hence, it is displayed as an output.
4. DATE()
Using the DATE () function in SQL, you can specifically extract the date from the DATETIME datatype column.
Syntax:
Example 1:
Write a query to display the date from the given date and time, i.e., 2021-10-24 18:28:44.
Query:
Here, we have written a SELECT query with the DATE () function to get only the date from the date and time parameter passed to the DATE() function. SHOW_DATE is an alias to store the date.
After executing the above query, we got the following output:
| SHOW_DATE |
|---|
| 2021-10-24 |
From the date and time parameter i.e., '2021-10-24 18:28:44', the date value is 2021-10-24. Hence, it is displayed as an output.
Example 2:
Write a query to display all the details from the student table with the date from the DateTime_Birth column of the student table.
Query:
Here, we have written a SELECT query to fetch the ID and Name. Date_of_Birth is an alias for DateTime_Birth. We have used the Date_of_Birth with the DATE () function to get only the date.
After executing the above query, we got the following output:
| ID | Name | Date_of_Birth | City |
|---|---|---|---|
| 1 | Mansi Shah | 2010-01-01 | Pune |
| 2 | Tejal Wagh | 2010-03-04 | Nasik |
| 3 | Sejal Kumari | 2010-05-01 | Mumbai |
| 4 | Sonal Jain | 2010-09-09 | Shimla |
| 5 | Surili Maheshwari | 2010-07-10 | Surat |
All the records are displayed as it is from the student table except for the DateTime_Birth column. Only the date is displayed in the Date_of_Birth column as per the requirement.
5. EXTRACT()
Using the EXTRACT() function in SQL, we can extract a specific part of date and time according to our requirements: day, month, year, day, hour, minute, etc.
Syntax:
Example 1:
Write a query to display the year from the given date, i.e., 24th October 2021.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the year from the given date. SHOW_YEAR is an alias to store the year.
After executing the above query, we got the following output:
| SHOW_YEAR |
|---|
| 2021 |
'2021' is the year in the given date. Hence, it is displayed as an output.
Example 2:
Write a query to display the month from the given date, i.e., 24th October 2021.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the month from the given date. SHOW_MONTH is an alias to store the month.
After executing the above query, we got the following output:
| SHOW_MONTH |
|---|
| 10 |
10th is the month value in the given date. Hence, it is displayed as an output.
Example 3:
Write a query to display the day from the given date, i.e., 24th October 2021.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the day from the given date. SHOW_DAY is an alias to store the day.
After executing the above query, we got the following output:
| SHOW_DAY |
|---|
| 24 |
24th is the day value in the given date. Hence, it is displayed as an output.
Example 4:
Write a query to display the hour from the given time, i.e., 19:10:43.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the hour from the given time. SHOW_HOUR is an alias to store the value of an hour.
After executing the above query, we got the following output:
| SHOW_HOUR |
|---|
| 19 |
'19' is the hour value in the given time. Hence, it is displayed as an output.
Example 5:
Write a query to display the minute from the given time, i.e., 19:10:43.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the minute value from the given time. SHOW_MINUTE is an alias to store the minute.
After executing the above query, we got the following output:
| SHOW_MINUTE |
|---|
| 10 |
'10' is the minute value in the given time. Hence, it is displayed as an output.
Example 6:
Write a query to display the seconds from the given time, i.e., 19:10:43.
Query:
Here, we have written a SELECT query with the EXTRACT () function to get the seconds value from the given time. SHOW_SECOND is an alias to store the value of a second.
After executing the above query, we got the following output:
| SHOW_SECOND |
|---|
| 43 |
43 is the second value in the given time. Hence, it is displayed as an output.
6. DATE_ADD()
Using the DATE_ADD () function in SQL, we can add a specific time interval to the given date.
Syntax:
Example 1:
Write a query to add an interval of 15 days to the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_ADD () function to add an interval of 15 days to the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 2021-11-08 |
After adding 15 days interval to 2021-10-24, the new date is 2021-11-08.
Example 2:
Write a query to add an interval of 5 months to the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_ADD () function to add an interval of 5 months to the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 2022-03-24 |
After adding 5 months interval to 2021-10-24, the new date is 2022-03-24.
Example 3:
Write a query to add an interval of 25 years to the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_ADD () function to add an interval of 25 years to the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 2046-10-24 |
After adding 25 years interval to 2021-10-24, the new date is 2046-10-24.
7. DATE_SUB()
Using the DATE_SUB () function in SQL, we can remove a specific time interval from the given date.
Syntax:
Example 1:
Write a query to remove an interval of 25 years from the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_SUB() function to remove an interval of 25 years from the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 1996-10-24 |
After removing 25 years interval from 2021-10-24, the new date is 1996-10-24.
Example 2:
Write a query to remove an interval of 5 months from the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_SUB() function to remove an interval of 5 months from the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 2021-05-24 |
After removing 5 months interval from 2021-10-24, the new date is 2021-05-24.
Example 3:
Write a query to remove an interval of 15 days from the given date, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_SUB() function to remove an interval of 15 days from the given date. NEW_DATE is an alias to store the value of a new date.
You will get the following output:
| NEW_DATE |
|---|
| 2021-10-09 |
After removing 15 days interval from 2021-10-24, the new date is 2021-10-09.
8. DATEDIFF()
Using the DATEDIFF() function in SQL will give us the number of days that fall between the two given dates.
Syntax:
Example 1:
Write a query to calculate the difference between two given dates, i.e., 24th October, 2021, and 9th October, 2021.
Query:
Here, we have written a SELECT query with DATE_DIFF() function to get the difference between the dates 2021-10-24 and 2021-10-09. Number_of_Days is an alias to store the difference in terms of the number of days.
You will get the following output:
| Number_of_Days |
|---|
| 15 |
There is a difference of 15 days between the dates 2021-10-24 and 2021-10-09.
Example 2:
Write a query to calculate the difference between two given dates, i.e., 5th May, 2018, and 5th May, 2008.
Query:
Here, we have written a SELECT query with DATE_DIFF () function to get the difference between the dates 2018-05-05 and 2008-05-05. Number_of_Days is an alias to store the difference in terms of the number of days.
You will get the following output:
| Number_of_Days |
|---|
| 3652 |
There is a difference of 3652 days between the dates 2018-05-05 and 2008-05-05.
9. DATE_FORMAT()
Using the DATE_FORMAT () function in SQL, we can display the date or time-related information in a well-formatted manner.
Syntax of using a DATE_FORMAT function on a table's column:
OR
Syntax to format a specific date:
Example 1:
Write a query to display the given date in a well-formatted manner, i.e., 24th October, 2021.
Query:
Here, we have written a SELECT query with DATE_FORMAT () function to get the date 2021-10-24 in the formatted form. Formatted_Date is an alias to store the formatted date.
You will get the following output:
| Formatted_Date |
|---|
| Sunday 24th October 2021 |
Sunday 24th October 2021 is the formatted date for 2021-10-24.
Example 2:
Write a query to display the given date and time in a well-formatted manner, i.e., 24th October, 2021 22:30:17.
Query:
Here, we have written a SELECT query with DATE_FORMAT () function to get the date and time '2021-10-24 22:30:17' in the formatted form. Formatted_DateTime is an alias to store the formatted date and time.
You will get the following output:
| Formatted_DateTime |
|---|
| Sunday 24th October 2021 10:30:17 PM |
Sunday 24th October 2021 10:30:17 PM is the formatted date and time for 2021-10-24 22:30:17.
Write a query to display all the details from the student column with the DateTime_Birth column in a well-formatted manner.
Query:
mysql> SELECT ID, Name, DATE_FORMAT (DateTime_Birth, "%W %D %M %Y %r") AS Formatted_DateTime, City FROM student;
Here, we have written a SELECT query with DATE_FORMAT () function to get the date and time for all the records in a formatted form. Formatted_DateTime is an alias to store the formatted date and time.
You will get the following output:
| ID | Name | Formatted_DateTime | City |
|---|---|---|---|
| 1 | Mansi Shah | Friday 1st January 2010 06:39:09 PM | Pune |
| 2 | Tejal Wagh | Thursday 4th March 2010 05:13:19 AM | Nasik |
| 3 | Sejal Kumari | Saturday 1st May 2010 10:31:07 AM | Mumbai |
| 4 | Sonal Jain | Thursday 9th September 2010 05:17:07 PM | Shimla |
| 5 | Surili Maheshwari | Saturday 10th July 2010 08:45:18 PM | Surat |
All the records are displayed as it is from the student table except for the DateTime_Birth column. Only the date and time are displayed in the Formatted_DateTime column as per the required.
