skip to main content
SQL Escape Sequences : Scalar Functions : Date and Time Functions
 

Date and Time Functions

The following table lists date and time functions. The following arguments can be used with the date and time functions:
• date_exp can be a column name, a date or timestamp literal, or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_DATE, or SQL_TIMESTAMP.
• time_exp can be a column name, a timestamp or timestamp literal, or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_TIME, or SQL_TIMESTAMP.
• timestamp_exp can be a column name; a time, date, or timestamp literal; or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_TIME, SQL_DATE, or SQL_TIMESTAMP.
 
Table 38: Scalar Time and Date Functions  
Function
Returns
CURDATE()
Current date as a date value.
CURRENT_DATE()
Current date.
CURRENT_TIME[(time-precision)]
Current local time. The time-precision argument determines the seconds precision of the returned value.
CURRENT_TIMESTAMP[(timestamp- precision)]
Current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp.
CURTIME()
Current local time as a time value.
DATEADD(datepart,number,date)
Returns a new date time value based on adding an interval to the specified date. The return date-time data type is same as the input date value.
DATEDIFF(datepart,startdate,enddate)
Returns the number of date and time boundaries crossed between two specified dates, where startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DAYNAME(date_exp)
Character string containing a data-source-specific name of the day for the day portion of date_exp.
DAYOFMONTH(date_exp)
Day of the month in date_exp as an integer value (1–31).
DAYOFWEEK(date_exp)
Day of the week in date_exp as an integer value
(1–7).
DAYOFYEAR(date_exp)
Day of the year in date_exp as an integer value
(1–366).
HOUR(time_exp)
Hour in time_exp as an integer value (0–23).
MINUTE(time_exp)
Minute in time_exp as an integer value (0–59).
MONTH(date_exp)
Month in date_exp as an integer value (1–12).
MONTHNAME(date_exp)
Character string containing the data source–specific name of the month.
NOW()
Current date and time as a timestamp value.
QUARTER(date_exp)
Quarter in date_exp as an integer value (1–4).
SECOND(time_exp)
Second in time_exp as an integer value (0–59).
TIMESTAMPADD(interval, integer_exp, time_exp)
Timestamp calculated by adding integer_exp intervals of type interval to time_exp. interval can be one of the following values:
SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
Fractional seconds are expressed in billionths of a second.
TIMESTAMPDIFF(interval, time_exp1, time_exp2)
Integer number of intervals of type interval by which time_exp2 is greater than time_exp1. interval has the same value as TIMESTAMPADD. Fractional seconds are expressed in billionths of a second.
WEEK(date_exp)
Week of the year in date_exp as an integer value (1–53).
YEAR(date_exp)
Year in date_exp. The range is data-source dependent.