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 ADDDATE("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