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:
|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
In this tutorial, we will be using the DataFlair table to understand the concepts. Let us view our demo database:
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
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
SELECT CURDATE() AS DateToday;
3. CURTIME( ) in SQL
SELECT CURTIME() AS timeNow;
4. DATE( ) in SQL
SELECT DATE(columnName),columnName2 FROM tableName;
SELECT name , DATE(dob) FROM DataFlair;
5. EXTRACT( ) in SQL
EXTRACT(unit FORM date);
SELECT name, EXTRACT(Day from dob) FROM DataFlair;
6. DATE_ADD( )
DATE_ADD(date, INTERVAL expr type);
SELECT name , DATE_ADD(dob,Interval 2 YEAR) AS post_2_years FROM DataFlair;
7. DATE_SUB( )
DATE_SUB(date, INTERVAL expr type);
SELECT name , DATE_SUB(dob,Interval 1 YEAR) AS post_2_years FROM DataFlair;
8. DATEDIFF( )
SELECT datediff('2020-2-25','2020-02-01') AS Age;
9. DATE_FORMAT( )
SELECT DATE_FORMAT(NOW(),'%d') as daytoday
12. YEAR( )
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.