SQL Date Functions | SQL Time Functions

In this tutorial, we will cover in detail about the date and time function in SQL. We have various in-built functions in SQL, which help us filter the useful data from a large set of data. One such part is the date and time functions of SQL.

Using the date and time functions we can find the date today, we can check the date after some given time and so on.

We also have the functionality to dissect the given date into various components like the month, the year, or just the day or a combination of all.

With this added functionality, we are able to utilize SQL for large-scale database designing and maintaining.

The date and time functions are fascinating and let us understand them more in our tutorial.

What is Date and Time Function in SQL?

In SQL, we have many data types available, which we can use as the date in our table. Some of them popularly being- ‘YYYY-MM-DD’ and ‘DD-MM-YYYY’.

In some scenarios, we also have time stored in our database with the date, in such cases, we need tools to separately access the time and the date. This is where the SQL time and functions come in handy.

Also, as a beginner, one should be very careful when using date or DateTime in the database as these are very likely to give exceptions if not dealt with properly.

Formats of Date Time in SQL:

DATE – YYYY-MM-DD
DATETIME – YYYY-MM-DD HH:MI:SS
TIMESTAMP – YYYY-MM-DD HH:MI:SS
YEAR – YYYY or YY

Stay updated with latest technology trends
Join DataFlair on Telegram!!

Why do we need Date and Time Functions?

We have plenty of functions available for date and time in SQL. These are provided to make sure smooth access of the date and time module while making and accessing a SQL database.

Some of the most popular date and time functions are as follows:

Sr.No Function Description
1 NOW( ) Displays the current date and time.
2 CURDATE( ) Displays the current date.
3 CURTIME( ) Displays the current time.
4 DATE( ) Displays the date from the Date/DateTime expression.
5 EXTRACT( ) Displays selected part i.e. date/time.
6 DAY( ) Displays the day from the given date.
7 MONTH( ) Displays the month from the given date.
8 YEAR( ) Displays the year from the given date.
9 DATE_ADD( ) Displays date after adding the given interval.
10 DATE_SUB( ) Displays date after subtracting the given interval.
11 DATEDIFF( ) Displays the interval between two dates.
12 DATE_FORMAT( ) Displays the date/time data in various formats available.

Examples of Date Time Functions in SQL

Demo Database

In this tutorial, we will be using the DataFlair table to understand the concepts. Let us view our demo database:
Query: 

SELECT * FROM DataFlair;

SQL DataFlair Database

Here we can see the data stored in the DataFlair database.

Now let us dive into the working of various functions:

1. NOW( ) in SQL

Syntax:

SELECT NOW();

Example:
Query: 

SELECT NOW() AS dateToday

SQL Now()

Here we can see the result i.e. the date today. You can go ahead and check on your system as well.

2. CURDATE( ) in SQL

Syntax: 

SELECT CURDATE();

Example:
Query:

SELECT CURDATE() AS DateToday;

SQL Curdate example

3. CURTIME( ) in SQL

Syntax:

SELECT CURTIME();

Example:
Query:

SELECT CURTIME() AS timeNow;

SQL Curtime Example

4. DATE( ) in SQL

Syntax: 

SELECT DATE(columnName),columnName2 FROM tableName;

Example:
Query: 

SELECT name , DATE(dob) FROM DataFlair;

SQL Date()

5. EXTRACT( ) in SQL

Syntax: 

EXTRACT(unit FORM date);

Example:
Query: 

SELECT name, EXTRACT(Day from dob) FROM DataFlair;

SQL Date Example

6. DATE_ADD( )

Syntax: 

DATE_ADD(date, INTERVAL expr type);

Example:
Query:

SELECT name , DATE_ADD(dob,Interval 2 YEAR) AS post_2_years FROM DataFlair;

SQL Example

7. DATE_SUB( )

Syntax: 

DATE_SUB(date, INTERVAL expr type);

Example:
Query: 

SELECT name , DATE_SUB(dob,Interval 1 YEAR) AS post_2_years FROM DataFlair;

SQL Date Subtract Example

8. DATEDIFF( )

Syntax: 

DATEDIFF(date1,date2);

Example:
Query: 

SELECT  datediff('2020-2-25','2020-02-01') AS Age;

SQL dateDiff Example

9. DATE_FORMAT( )

Syntax: 

SELECT DATE_FORMAT(date);

Example:
Query: 

SELECT DATE_FORMAT(NOW(),'%d') as daytoday

SQL Date Format Example

10. DAY()

Syntax: 

SELECT DAY(date);

Example:
Query: 

SELECT DAY(NOW());

SQL day()

11. MONTH()

Syntax: 

SELECT MONTH(date);

Example:
Query:

SELECT MONTH(NOW());

SQL Month()

12. YEAR( )

Syntax:

SELECT YEAR(date);

Example:
Query:

SELECT YEAR(NOW());

SQL year()

Summary

In this tutorial, we have understood date and time functions in-depth, with the use and syntax of each available to us. With the examples, we were able to understand how powerful SQL date and time functions are.

Using the date and time functions, we can practically extract any detail we require in a query. With the use of Now() we are able to get the current date and further, we can format the date using the various formatting options available.

If you are Happy with DataFlair, do not forget to make us happy with your positive feedback on Google | Facebook

1 Response

  1. Animesh Singh says:

    Article is incomplete at 3rd point

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.