SQL | Date functions

In SQL, dates are complicated for newbies, since while working with database, the format of the date in 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.
In MySql the default date functions are:

  • NOW(): Returns the current date and time. Example:
    SELECT NOW();
    

    Output:

    2017-01-13 08:03:52
    
  • CURDATE(): Returns the current date. Example:
    SELECT CURDATE();
    

    Output:

    2017-01-13
    
  • CURTIME(): Returns the current time. Example:
    SELECT CURTIME();
    

    Output:

    08:05:15
    
  • DATE(): Extracts the date part of a date or date/time expression. Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581
    SELECT Name, DATE(BirthTime) AS BirthDate FROM Test;
    

    Output:

    Name BirthDate
    Pratik 1996-09-26
  • EXTRACT(): Returns a single part of a date/time. Syntax:
    EXTRACT(unit FORM date);
    

    There are several units that can be considered but only some are used such as:
    MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc.
    And ‘date’ is a valid date expression.

    Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581

    Queries

    • SELECT Name, Extract(DAY FROM BirthTime) AS BirthDay FROM Test;
      

      Output:

      Name BirthDay
      Pratik 26
    • SELECT Name, Extract(YEAR FROM BirthTime) AS BirthYear FROM Test;
      

      Output:

      Name BirthYear
      Pratik 1996
    • SELECT Name, Extract(SECOND FROM BirthTime) AS BirthSecond FROM Test;
      

      Output:

      Name BirthSecond
      Pratik 581
  • DATE_ADD() : Adds a specified time interval to a date
    Syntax:

    DATE_ADD(date, INTERVAL expr type);
    

    Where,  date – valid date expression and expr is the number of interval we want to add.
    and type can be one of the following:
    MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc.

    Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581

    Queries

    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 1 YEAR) AS BirthTimeModified FROM Test;
      

      Output:

      Name BirthTimeModified
      Pratik 1997-09-26 16:44:15.581
    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 30 DAY) AS BirthDayModified FROM Test;
      

      Output:

      Name BirthDayModified
      Pratik 1996-10-26 16:44:15.581
    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 4 HOUR) AS BirthHourModified FROM Test;
      

      Output:

      Name BirthSecond
      Pratik 1996-10-26 20:44:15.581
  • DATE_SUB(): Subtracts a specified time interval from a date. Syntax for DATE_SUB is same as DATE_ADD just the difference is that DATE_SUB is used to subtract a given interval of date.
  • DATEDIFF(): Returns the number of days between two dates.Syntax:
    DATEDIFF(date1, date2);
    date1 & date2- date/time expression
    

    Example:

    SELECT DATE_DIFF('2017-01-13','2017-01-03') AS DateDiff;
    

    Output:

    DateDiff
    10
  • DATE_FORMAT(): Displays date/time data in different formats.Syntax:
    DATE_FORMAT(date,format);
    

    date is a valid date and format specifies the output format for the date/time. The formats that can be used are:

    • %a-Abbreviated weekday name (Sun-Sat)
    • %b-Abbreviated month name (Jan-Dec)
    • %c-Month, numeric (0-12)
    • %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
    • %d-Day of month, numeric (00-31)
    • %e-Day of month, numeric (0-31)
    • %f-Microseconds (000000-999999)
    • %H-Hour (00-23)
    • %h-Hour (01-12)
    • %I-Hour (01-12)
    • %i-Minutes, numeric (00-59)
    • %j-Day of year (001-366)
    • %k-Hour (0-23)
    • %l-Hour (1-12)
    • %M-Month name (January-December)
    • %m-Month, numeric (00-12)
    • %p-AM or PM
    • %r-Time, 12-hour (hh:mm:ss followed by AM or PM)
    • %S-Seconds (00-59)
    • %s-Seconds (00-59)
    • %T-Time, 24-hour (hh:mm:ss)
    • %U-Week (00-53) where Sunday is the first day of week
    • %u-Week (00-53) where Monday is the first day of week
    • %V-Week (01-53) where Sunday is the first day of week, used with %X
    • %v-Week (01-53) where Monday is the first day of week, used with %x
    • %W-Weekday name (Sunday-Saturday)
    • %w-Day of the week (0=Sunday, 6=Saturday)
    • %X-Year for the week where Sunday is the first day of week, four digits, used with %V
    • %x-Year for the week where Monday is the first day of week, four digits, used with %v
    • %Y-Year, numeric, four digits
    • %y-Year, numeric, two digits

    Example:

    DATE_FORMAT(NOW(),'%d %b %y')
    

    Result:

    13 Jan 17
    

This article is contributed by Pratik Agarwal. 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 write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up


Article Tags :
Practice Tags :


3


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