Introduction to SQL Date Function
Working with the database is not very difficult as one needs to know the basic queries and keywords that should be used to retrieve, update and delete the data in the database. SQL allows us to store the structured data (data in the form of rows and columns) and provides simple queries to work on it. In this topic, we are going to discuss SQL Date Function.
The date is one of the most important functions used in the SQL but it is somewhat difficult for newbies to understand it as there are many formats in which date can be stored in the database and in different formats the user wants to retrieve it depending on the specific requirements. In SQL DateTime (time is also used along with the date) is frequently used to store both the date and time values at a time in a single column.
Date Functions of SQL
Let’s understand each date function used in SQL one by one in detail:
1. NOW()
NOW() is used to return the current system date and time value.
Query:
SELECT NOW();
Output: 2019-08-06 08:10:12
2. CURDATE()
CURDATE() is used to return the current system date.
Query:
SELECT CURDATE();
Output: 2019-08-06
3. CURTIME()
CURTIME() is used to return the current system time.
Query:
SELECT CURTIME();
Output: 08:10:12
4. DATE(expr)
Extracts only the date part from the column having the DateTime value.
Query:
SELECT DATE(‘2019-08-06 08:10:12’);
Output: 2019-08-06
5. EXTRACT(unit FROM DATE)
EXTRACT() function is used to retrieve a particular part of the date/time value.
4.5 (4,522 ratings)
View Course
Consider the following table ‘student’ for this:
|
id |
name | birthday |
| 1001 | Ankita |
2019-08-06 08:10:12 |
Query:
SELECT id, name, EXTRACT(DAY FRO from the student; M birthday) AS day_of_birth
|
id |
name | day_of_birth |
| 1001 | Ankita |
08 |
6. DATEADD(date, INTERVAL expr unit):
This function is used in the scenarios when we want to display date by adding some value to the original value.6. DATEADD(date, INTERVAL expr unit)
Query:
SELECT DATEADD(‘2019-06-05’, INTERVAL 5 DAY);
Output: 2019-06-10
Here Unit which is DAY in the above example can have different values :
-MICROSECOND
-SECOND-DAY
-HOUR-WEEK
-YEAR-DAY
-MONTH-DAY
-HOUR-DAY
-SECOND-QUARTER
-MINUTE_SECOND
-HOUR_SECOND
7. DATEDIFF()
This function returns the difference of the number of days from date2 to date1
Query:
SELECT DATEDIFF(‘2019-06-03’ , ‘2019-06-05’);
Output: 2
8. DATE_FORMAT(date, format)
This is one of the important functions used in SQL.
This function returns the date in various formats depending on the specifier mentioned by the user.
| Specifier | Description |
| %a | DIsplays abbreviated weekday name (Sun-Sat) |
| %D | Displays day of the month with English suffix (1st, 2nd, 3rd..) |
| %d | Displays day of the month in numeric values (00..31) |
| %b | DIsplays abbreviated month name (Jan-Dec) |
| %c | Displays month in numeric values (1, 2, 3…) |
| %h | Displays hour (01-12) |
| %H | Displays hour (00-23) |
| %m | Displays month name in numeric (0-12) |
| %M | Displays month name in English language (January- December) |
| %w | Displays day of the week (0-6) |
| %W | Displays weekday name (Sunday-Saturday) |
| %s | Displays seconds (00..59) |
| %S | Displays seconds (00..59) |
| %y | Displays numeric year in two digits |
| %Y | Displays numeric year in four digits |
Query:
SELECT DATE_FORMAT(‘2019-06-03 09:40:05’, %W %M %y);
Output: Monday, June 19
9. DAYNAME(date)
This function returns the name of the date for the date specified.
Query:
DAYNAME(‘2019-06-03’);
Output: Monday
10. DAYOFMONTH(date)
This function returns the day of the month for the date specified.
Query:
DAYOFMONTH(‘2019-06-03’);
Output: 3
11. DAYOFWEEK(date)
This function returns the weekday index of the date given. Indexes assigned are Sunday=1, Monday=2, Tuesday=3, and so on.
Query:
DAYOFWEEK(‘2019-06-03’);
Output: 2
12. DAYOFYEAR(date)
This function returns the day of the month for a year for the date specified. Ranges from 1 to 366.
Query:
DAYOFYEAR(‘2019-06-03’);
Output: 154
13. DATE_SUB(date, INTERVAL expr unit)
This function is used to subtract the given date/time values from the date specified
Query:
SELECT DATE_SUB(‘2019-06-03’, INTERVAL 4 DAY);
Output: 2019-05-31
Like the DATE_ADD() function, there can be various INTERVAL values.
14. MAKEDATE(year, days)
This function creates a date based on the year and the days provided by the user. But the year provided should be of 4 digits
Query:
SELECT MAKEDATE(2019,154);
Output: 2019-06-03
15. MAKETIME(hour, minute, second)
This function returns the time based on the hour, minute and second value provided by the user.
Query:
SELECT MAKETIME(09,20,05);
Output: 09:20:05
Other Date Functions
| S.N
o. |
Function
Name |
Description | Example | Output |
| 1. | LAST_DAY(date) | Takes the date or DateTime value and returns the last day of the corresponding month | SELECT LAST_DAY(‘2019-06-09’) | 2019-06-30 |
| 2. | FROM_DAYS(n) | Takes n number of days as input and returns the date value | SELECT FROM_DAYS(685552) | 1876-12-23 |
| 3. | HOUR(time) | Takes the time value and returns the hour value | SELECT
HOUR(‘03:24:27’) |
03 |
| 4. | MINUTE(time) | Takes the time values as input and returns the minutes value | SELECT
MINUTE(‘03:27:24’) |
27 |
| 5. | MONTH(date) | Takes the date as input and returns the corresponding month of the date given | SELECT
MONTH(‘2019-06-09’) |
06 |
| 6. | SYSDATE() | Returns the current date and time value in the format YYYY MM
DD HH:MM:SS |
SELECT SYSDATE() | 2019-06-09 11:15:08 |
| 7. | TIME_TO_SEC(time) | Take time as input and returns total seconds corresponding to that time | SELECT
TIME_TO_SEC(‘11:15 :10’) |
40510 |
| 8. | YEAR(date) | Take date as input and returns the year for the corresponding date | SELECT YEAR
(‘19-06-09’) |
2019 |
| in the range of 1000-9999 | ||||
| 9. | TIMESTAMP(expr) | Returns the date or datetime expression | SELECT TIMESTAMP
(‘2019-06-09’) |
2019-06-09 00:00:00 |
| 10. | WEEKDAY(date) | Take date as input and returns the weekday index corresponding to that date (0= Monday, 1= Tuesday, 2=
Wednesday, so on) |
SELECT WEEKDAY
(‘2019-06-09’) |
6 |
Conclusion
Above mentioned are some of the basic date functions used in SQL. There are several other date functions used in different scenarios. One must need to keep in mind the syntax and the argument passed in the function before using any of them to avoid unexpected results.
Recommended Articles
This is a guide to SQL Date Function. Here we discuss the Date Function used in SQL one by one with Examples and Outputs in detail. You may also have a look at the following articles to learn more –
