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:

  1. NOW()
  2. CURDATE()
  3. CURTIME()
  4. DATE()
  5. EXTRACT()
  6. DATE_ADD()
  7. DATE_SUB()
  8. DATEDIFF()
  9. 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:

IDNameDateTime_BirthCity
1Mansi Shah2010-01-01 18:39:09Pune
2Tejal Wagh2010-03-04 05:13:19Nasik
3Sejal Kumari2010-05-01 10:31:07Mumbai
4Sonal Jain2010-09-09 17:17:07Shimla
5Surili Maheshwari2010-07-10 20:45:18Surat

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:

IDNameDate_of_BirthCity
1Mansi Shah2010-01-01Pune
2Tejal Wagh2010-03-04Nasik
3Sejal Kumari2010-05-01Mumbai
4Sonal Jain2010-09-09Shimla
5Surili Maheshwari2010-07-10Surat

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.

Example 3:

Write a query to display all the details from the student column with the DateTime_Birth column in a well-formatted manner.

Query:

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:

IDNameFormatted_DateTimeCity
1Mansi ShahFriday 1st January 2010 06:39:09 PMPune
2Tejal WaghThursday 4th March 2010 05:13:19 AMNasik
3Sejal KumariSaturday 1st May 2010 10:31:07 AMMumbai
4Sonal JainThursday 9th September 2010 05:17:07 PMShimla
5Surili MaheshwariSaturday 10th July 2010 08:45:18 PMSurat

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.






Latest Courses