Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL | Date Functions (Set-1)

  • Difficulty Level : Basic
  • Last Updated : 20 Jul, 2018

In SQL, dates are complicated for newbies, since while working with a database, the format of the date in the table must be matched with the input date in order to insert. In various scenarios instead of date, datetime (time is also involved with date) is used.

Some of the important date functions have been already discussed in the previous post. The basic idea of this post is to know the working or syntax of all the date functions:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

Below are the date functions that are used in SQL:

  1. ADDDATE(): It returns a date after a certain time/date interval has been added.
    Syntax: SELECT ADDTIME("2018-07-16 02:52:47", "2");

    Output: 2018-07-16 02:52:49

  2. ADDTIME(): It returns a time / date time after a certain time interval has been added.
    Syntax: SELECT ADDTIME("2017-06-15 09:34:21", "2");

    Output: 2017-06-15 09:34:23

  3. CURDATE(): It returns the current date.
    Syntax: SELECT CURDATE();

    Output: 2018-07-16

  4. CURRENT_DATE(): It returns the current date.
    Syntax: SELECT CURRENT_DATE();

    Output: 2018-07-16

  5. CURRENT_TIME(): It returns the current time.
    Syntax: SELECT CURRENT_TIME();

    Output: 02:53:15

  6. CURRENT_TIMESTAMP(): It returns the current date and time.
    Syntax: SELECT CURRENT_TIMESTAMP();

    Output: 2018-07-16 02:53:21

  7. CURTIME(): It returns the current time.
    Syntax: SELECT CURTIME();

    Output: 02:53:28

  8. DATE(): It extracts the date value from a date or date time expression.
    Syntax: SELECT DATE("2017-06-15");

    Output: 2017-06-15

  9. DATEDIFF(): It returns the difference in days between two date values.
    Syntax: SELECT DATEDIFF("2017-06-25", "2017-06-15");

    Output: 10

  10. DATE_ADD(): It returns a date after a certain time/date interval has been added.
    Syntax: SELECT DATE_ADD("2018-07-16", INTERVAL 10 DAY);

    Output: 2018-07-16

  11. DATE_FORMAT(): It formats a date as specified by a format mask.
    Syntax: SELECT DATE_FORMAT("2018-06-15", "%Y");

    Output: 2018

  12. DATE_SUB(): It returns a date after a certain time/date interval has been subtracted.
    Syntax: SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);

    Output: 2018-07-16

  13. DAY(): It returns the day portion of a date value.
    Syntax: SELECT DAY("2018-07-16");

    Output: 16

  14. DAYNAME(): It returns the weekday name for a date.
    Syntax: SELECT DAYNAME('2008-05-15');

    Output: Thursday

  15. DAYOFMONTH(): It returns the day portion of a date value.
    Syntax: SELECT DAYOFMONTH('2018-07-16');

    Output: 16

  16. DAYWEEK(): It returns the weekday index for a date value.
    Syntax: SELECT WEEKDAY("2018-07-16");

    Output: 0

  17. DAYOFYEAR(): It returns the day of the year for a date value.
    Syntax: SELECT DAYOFYEAR("2018-07-16");

    Output: 197

  18. EXTRACT(): It extracts parts from a date.
    Syntax: SELECT EXTRACT(MONTH FROM "2018-07-16");

    Output: 7

  19. FROM_DAYS(): It returns a date value from a numeric representation of the day.
    Syntax: SELECT FROM_DAYS(685467);

    Output: 1876-09-29

  20. HOUR(): It returns the hour portion of a date value.
    Syntax: SELECT HOUR("2018-07-16 09:34:00");

    Output: 9

  21. LAST_DAY(): It returns the last day of the month for a given date.
    Syntax: SELECT LAST_DAY('2018-07-16');

    Output: 2018-07-31

  22. LOCALTIME(): It returns the current date and time.
    Syntax: SELECT LOCALTIME();

    Output: 2018-07-16 02:56:42

  23. LOCALTIMESTAMP(): It returns the current date and time.
    Syntax: SELECT LOCALTIMESTAMP();

    Output: 2018-07-16 02:56:48

  24. MAKEDATE(): It returns the date for a certain year and day-of-year value.
    Syntax: SELECT MAKEDATE(2009, 138);

    Output: 2009-05-18

  25. MAKETIME(): It returns the time for a certain hour, minute, second combination.
    Syntax: SELECT MAKETIME(11, 35, 4);

    Output: 11:35:04

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!