EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL Date Function
 

SQL Date Function

Yashi Goyal
Article byYashi Goyal
EDUCBA
Reviewed byRavi Rathore

Updated March 21, 2023

SQL Date Function

 

 

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 the SQL Date Function.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

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 the numeric year in two digits
%Y Displays the 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

The 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

We hope that this EDUCBA information on “SQL Date Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Foreign Key in SQL
  2. SQL Insert Query
  3. Types of Joins in SQL
  4. JavaScript Date Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW