Time and Date Functions

Date and time functions can be used to generate, process, and manipulate data that consists of date or time data types, such as DATE and TIME.

Inserting Time and Date Function Values

If you wish to insert the value of a function into a table, one method supported by all the time/date functions is to use a SELECT subquery in your INSERT statement, as shown:

INSERT INTO t1 (c1, c2) SELECT CURRENT_DATE(), 
CURRENT_TIME()

Some functions, such as CURDATE(), CURTIME(), and NOW(), also support direct INSERT, as shown:

INSERT INTO t1 (c1) VALUES (CURDATE())

Table 5-3 Time and Date Functions
Function	Description
CURDATE ( )	Returns the current local date in this format: `yyyy-mm-dd'. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURDATE() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
CURRENT_DATE ( )	Returns the current UTC date in this format: `yyyy-mm-dd'
CURTIME ( )	Returns the current local time in this format: `hh:mm:ss'. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURTIME() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
CURRENT_TIME ( )	Returns the current UTC time in this format: `hh:mm:ss'
CURRENT_TIMESTAMP ( )	Returns the current UTC date and time as a timestamp value in this format: `yyyy-mm-dd hh:mm:ss.sssssss'
DATEADD (datepart, interval, date_exp)	Returns a new DATETIME value based on adding an interval to a date. For example, a datapart day, an interval of 11, and a date_exp of January 26, 2020, returns February 6, 2020. Datepart specifies the part of the date to return, and must be one of the following values. Acceptable abbreviations for the value are in parentheses. year quarter month day dayofyear week hour minute second millisecond Interval specifies an integer value used to increment datepart. If interval contains a fractional portion, the fraction part is ignored. Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME .
DATEDIFF (datepart, start, end)	Returns an integer for the difference between the two dates. The integer is the number of date and time boundaries crossed between the two dates. For example, suppose that table mytest has two columns,col1 and col2, both of which are DATETIME. The value in col1 is 2000-01-01 11:11:11.234 and the value in col2 is 2004-09-11 10:10:10.211. The following SELECT statement returns 56, because that is the difference in months between col1 and col2: SELECT DATEDIFF(month, col1, col1) as Month_Difference FROM mytest. Datepart specifies the part of the date on which to calculate the difference, and must be one of the following values. year quarter month day dayofyear week hour minute second millisecond Start specifies the beginning date for the difference calculation. Start is an expression that returns a DATETIME value or a Unicode character string in a DATE format. End specifies the ending date for the difference calculation. End is an expression that returns a DATETIME value or a Unicode character string in a DATE format. Start is subtracted from end. An error is returned if the return value is outside of the range for integer values. See Data Type Ranges .
DATENAME (datepart, date_exp)	Returns a character string (a VARCHAR) that represents the datepart of date_exp. For example, a datepart month returns the name of the month such as January, February, and so forth. A datepart weekday returns the day of the week such as Monday, Tuesday, and so forth (Sunday = 1, Saturday = 7). Datepart the part of the date to return, and must be one of the following values. year quarter month day dayofyear week weekday hour minute second millisecond Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME .
DATEPART (datepart, date_exp)	Returns an integer that represents the datepart of date_exp. For example, a datepart month returns an integer representing the month (January = 1, December = 12). A datepart weekday returns an integer representing the day of the week (Sunday = 1, Saturday = 7). Datepart the part of the date to return, and must be one of the following values. Acceptable abbreviations for the value are in parentheses. year quarter month day dayofyear week weekday hour minute second millisecond Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME .
DAYNAME (date_exp)	Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English, or Sonntag through Samstag for a data source that uses German) for the day portion of date_exp. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
DAYOFMONTH (date_exp)	Returns the day of the month in date_exp as an integer in the range of 1 to 31. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
DAYOFYEAR (date_exp)	Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366.
EXTRACT (extract_field, extract_source)	Returns the extract_field portion of the extract_source. The extract_source argument is a date, time or interval expression. The permitted values of extract_field are: YEAR MONTH DAY HOUR MINUTE SECOND These values are returned from the target expression.
HOUR (time_exp)	Returns the hour as an integer in the rage of 0 to 23. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MINUTE (time_exp)	Returns the minute as an integer in the range 0 to 59. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MONTH (date_exp)	Returns the month as an integer in the range of 1 to 12. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
MONTHNAME (date_exp)	Returns a character string containing the data source-specific name of the month (for example, September through December or Sept. through Dec. for a data source that uses English, or Settembre through Dicembre for a data source that uses Italian) for the month portion of date_exp. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
NOW ( )	Returns the current local date and time as a timestamp value in this format: `yyyy-mm-dd hh:mm:ss.sssssss' Uses the local clock time by default. If SET TIME ZONE has been called, then the value of NOW() is determined by calculating UTC time/date from the system clock and operating system locale setting, then adding the displacement value specified with SET TIME ZONE.
QUARTER (date_exp)	Returns the quarter in date_exp as an integer value in the range of 1- 4, where 1 represents January 1 through March 31. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
SECOND (time_exp)	Returns the second as an integer in the range of 0 to 59. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
TIMESTAMPADD (interval, integer_exp, timestamp_exp)	Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. The allowed values for interval are: SQL_TSI_YEAR SQL_TSI_QUARTER SQL_TSI_MONTH SQL_TSI_WEEK SQL_TSI_DAY SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_SECOND
TIMESTAMPDIFF (interval, timestamp_exp1, timestamp_exp2)	Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. The values allowed for interval are the same as for TIMESTAMPADD
WEEK (date_exp)	Returns the week of the year based on the week field in date_exp as an integer in the range of 1 to 53. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.
YEAR (date_exp)	Returns the year as an integer value. The range depends on the data source. Date_exp can be a DATE, TIMESTAMP literal, or a column containing DATE, TIMESTAMP, or DATETIME data.

Examples

The following example shows how to insert values from time or date functions. Some functions, such as CURDATE(), you can use directly in an INSERT statement. Other functions, however, are not supported in this manner. The method with the widest support is to use INSERT with SELECT. In the example below, the UTC time value returned by CURRENT_TIME() is inserted into table T1:

INSERT INTO T1 (C1) SELECT CURRENT_TIME()

The following example illustrates the use of hour.

SELECT c.Name,c.Credit_Hours FROM Course c WHERE c.Name 
= ANY (SELECT cl.Name FROM Class cl WHERE c.Name = 
cl.Name AND c.Credit_Hours >(HOUR (Finish_Time - 
Start_Time) + 1))

The following is an example of minute.

SELECT minute(log) FROM billing

The following example illustrates the use of second.

SELECT SECOND(log) FROM billing 
SELECT log FROM billing where SECOND(log) = 31

The following example illustrates the use of NOW().

SELECT now() - log FROM billing

The following is a complex example that uses month, day, year, hour and minute.

SELECT Name, Section, MONTH(Start_Date), 
DAY(Start_Date), YEAR(Start_Date), HOUR(Start_Time),  
MINUTE(Start_Time) FROM Class

The following example illustrates use of CURDATE().

SELECT ID, Name, Section FROM Class WHERE (Start_Date - 
CURDATE()) <= 2 AND (Start_Date - CURDATE()) >= 0

The next example gives the day of the month and day of the week of the start date of class from the class table.

SELECT  DAYOFMONTH(Start_date), DAYOFWEEK(Start_date)  
from Class 
SELECT * FROM person WHERE YEAR(Date_Of_Birth) < 1970