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

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:

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;

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

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;

3. CURTIME( ) in SQL

Syntax:

SELECT CURTIME();

Example:
Query:

SELECT CURTIME() AS timeNow;

4. DATE( ) in SQL

Syntax: 

SELECT DATE(columnName),columnName2 FROM tableName;

Example:
Query: 

SELECT name , DATE(dob) FROM DataFlair;

5. EXTRACT( ) in SQL

Syntax: 

EXTRACT(unit FORM date);

Example:
Query: 

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

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;

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;

8. DATEDIFF( )

Syntax: 

DATEDIFF(date1,date2);

Example:
Query: 

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

9. DATE_FORMAT( )

Syntax: 

SELECT DATE_FORMAT(date);

Example:
Query: 

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

10. DAY()

Syntax: 

SELECT DAY(date);

Example:
Query: 

SELECT DAY(NOW());

11. MONTH()

Syntax: 

SELECT MONTH(date);

Example:
Query:

SELECT MONTH(NOW());

12. YEAR( )

Syntax:

SELECT YEAR(date);

Example:
Query:

SELECT YEAR(NOW());

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.