|
Function
|
Purpose
|
|---|---|
|
ADD_MONTHS(expression, numeric-expression)
|
Returns a datetime value that represents expression plus a specified number of months.
expression specifies the starting date. The expression must return a value of one of the following built-in data types: a DATE or a TIMESTAMP.
numeric-expression returns a value of any built-in numeric data type. If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. numeric-expression specifies the number of months to add to the starting date specified by expression. A negative numeric value is allowed.
|
|
CURDATE()
CURRENT_DATE
CURRENT DATE
|
Returns the current date
|
|
CURTIME()
CURRENT_TIME
CURRENT TIME
|
Returns the current time
|
|
CURRENT_TIMESTAMP
CURRENT TIMESTAMP
|
Returns the current date and time as a timestamp
|
|
DATE(expression)
|
Returns a date from a value.
expression must be a DATE, TIMESTAMP, a positive number less than or equal to 3 652 059, or a valid string representation of a date or timestamp.
▪ If expression is a DATE, TIMESTAMP, or valid string representation of a date or timestamp, the result is the date part of the value.
▪ If expression is a string of length 7, it must represent a valid date in the form YYYYNNN, where YYYY are digits denoting a year, and NNN are digits between 001 and 366, denoting a day of that year.
▪ If expression is a number, the result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number.
|
|
DAYNAME(date)
|
Returns a string with the day of the week
|
|
DAYOFMONTH(date)
|
Returns the day of the month as an integer between 1 and 31
|
|
DAYOFWEEK(date)
|
Returns the day of the week as an integer between 1 and 7, where 1 represents Sunday
|
|
DAYOFYEAR(date)
|
Returns the day of the year as an integer between 1 and 366
|
|
EXTRACT (date field FROM date_exp)
|
Isolates a single field of a datetime or a interval and converts it to a number.
|
|
HOUR(time_exp)
|
Returns the hour as an integer between 0 and 23
|
|
MINUTE(time_exp)
|
Returns the minute as an integer between 0 and 59
|
|
MONTH(date)
|
Returns the month as an integer between 1 and 12
|
|
MONTHNAME(date)
|
Returns the month name as a string
|
|
NOW()
|
Returns the current date and time as a timestamp
|
|
QUARTER(date)
|
Returns the quarter as an integer between 1 and 4
|
|
SECOND(time_exp)
|
Returns the second as an integer between 0 and 59
|
|
SYSDATE
|
Returns the current date and time as a timestamp
|
|
TIMESTAMPADD(interval, integer_exp, timestamp_exp)
|
Calculates a timestamp by adding integer_exp intervals of type interval to timestamp_exp
Keywords used to express valid TIMESTAMPADD interval values are:
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
|
|
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)
|
Returns the interval number of intervals by which timestamp_exp1 is greater than timestamp_exp2
Keywords used to express valid TIMESTAMPADD interval values are:
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
|
|
TIMESTAMP_FORMAT(input-string, format-string)
TO_DATE(input-string, format-string)
TO_TIMESTAMP(input-string, format-string)
|
Returns a timestamp that is based on the interpretation of input-string using the specified format (format-string). If the timestamp precision of the timestamp expression is less than what is specified by the format, zero digits are padded onto the right of the specified digits.
The following formats are supported:
▪ YY Last two digits of the year (00-99)
▪ YYYY 4-digit year (0000-9999)
▪ RR behaves the same as YY
▪ RRRR behaves the same as YYYY
▪ MM Month (01-12)
▪ DD Day of month (01-31)
▪ HH24 Hour of the day (00-24) in 24-hour format
▪ MI Minute (00-59)
▪ SS Seconds (00-59)
▪ FF or FFn Fractional seconds (0-999999999999).
The number n is used to specify the number of digits to include in the returned value. Valid values for n are 1-12 with no leading zeros.
Specifying FF is equivalent to specifying FF6.
The TO_DATE, TIMESTAMP_FORMAT, and TO_TIMESTAMP functions are synonymous.
|
|
WEEK(date)
|
Returns the week of the year as an integer between 1 and 52
|
|
YEAR(date)
|
Returns the year as an integer
|