SQL | Date Functions (Set-1)

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:

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

I like to do coding in C++C and java programming languages HTML and CSS always intersts me Sharing knowleged is the best way according to me to increase ones knwoledge

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


3


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.