|
Function
|
Description
|
|
CURDATE()
|
Returns the current date. For example:
SELECT CURDATE() FROM emp;
Returns the current date as: 2008-10-25
|
|
CURTIME()
|
Returns the current local time. For example:
SELECT CURTIME() FROM emp;
Returns the current time as: 10:20:05
|
|
CURTIMESTAMP()
|
Returns the current local date and local time as a timestamp value. For example:
SELECT CURTIMESTAMP() FROM emp;
Returns current date and time as: 2003-03-31 14:08:57
|
|
DATEADD(datepart, number, date)
TIMESTAMPADD(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.
datepart: Is the parameter that specifies on which part of the date to return a new value. Both ODBC notation and SQL Server notation for datepart are supported.
Datepart Abbreviations
Year SQL_TSI_YEAR, year, yy, yyyy,
quarter SQL_TSI_QUARTER, quarter, qq, q
Month SQL_TSI_MONTH, month, mm, m
dayofyear DAYOFYEAR, dy, y
Day SQL_TSI_DAY, day, dd, d
Week SQL_TSI_WEEK, week, wk, ww
Hour SQL_TSI_HOUR, hour, hh
minute SQL_TSI_MINUTE, minute, mi, n
second SQL_TSI_SECOND, second, ss, s
The current implementation does not support millisecond and fractional second specifications.
number: Is the value used to increment the datepart. If value is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and 1.75 for number, date is incremented by 1.
date: Is an expression that returns date or timestamp value or a character string in a date-time format.
|
|
DATEDIFF(datepart, startdate, enddate)
TIMESTAMPDIFF(datepart, startdate, enddate)
|
Returns the number of date and time boundaries crossed between two specified dates. startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
datepart: Is the parameter that specifies on which part of the date to calculate the difference. Both ODBC notation and SQL notation for datepart are supported.
Datepart Abbreviations
Year SQL_TSI_YEAR, year, yy, yyyy,
quarter SQL_TSI_QUARTER, quarter, qq, q
Month SQL_TSI_MONTH, month, mm, m
dayofyear DAYOFYEAR, dy, y
Day SQL_TSI_DAY, day, dd, d
Week SQL_TSI_WEEK, week, wk, ww
Hour SQL_TSI_HOUR, hour, hh
minute SQL_TSI_MINUTE, minute, mi, n
second SQL_TSI_SECOND, second, ss, s
The current implementation does not support dayofyear, millisecond and factional second specifications. For example:
SELECT DATEDIFF(year, hiredate, curdate())
FROM emp WHERE hiredate = '2000-10-01';
SELECT DATEDIFF(SQL_TSI_YEAR, hiredate, curdate())
FROM emp WHERE hiredate = '2000-10-01';
This returns 2 (assuming that the curdate() returns year 2002).
|
|
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. For example:
SELECT DAYNAME('2002-01-01'), DAYNAME('2002-01-02') FROM emp;
This returns 'Tuesday' and 'Wednesday'.
|
|
DAYOFMONTH(date_exp)
|
Returns the day of the month based on the month field in date_exp as an integer value in the range of 1–31. For example:
SELECT DAYOFMONTH('2002-01-05') FROM emp;
This returns 5.
|
|
DAYOFWEEK(date_exp)
|
Returns the day of the week based on the week field in date_exp as an integer value in the range of 1–7, where 1 represents Sunday. For example:
SELECT DAYOFWEEK('2002-01-05') FROM emp;
This returns 7.
|
|
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. For example:
SELECT DAYOFYEAR('2002-01-05') FROM emp;
This returns 5.
|
|
HOUR(time_exp)
|
Returns the hour based on the hour field in time_exp as an integer value in the range of 0–23. For example:
SELECT HOUR('22:20:20') FROM emp;
This returns 22.
|
|
MINUTE(time_exp)
|
Returns the minute based on the minute field in time_exp as an integer value in the range of 0–59. For example:
SELECT MINUTE('22:21:20') FROM emp;
This returns 21.
|
|
MONTH(date_exp)
|
Returns the month based on the month field in date_exp as an integer value in the range of 1–12. For example:
SELECT MONTH('2002-01-05') FROM emp;
This returns 1.
|
|
MONTHNAME(date_exp)
|
Returns a character string containing the data source–specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English, or Januar through Dezember for a data source that uses German) for the month portion of date_exp. For example:
SELECT MONTHNAME('2002-01-05') FROM emp;
This returns January.
|
|
NOW( )
|
Returns current date and time as a timestamp value. For example:
SELECT NOW() FROM emp;
This returns the current date and time: 2002-10-25 10:20:05.
|
|
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. For example:
SELECT QUARTER('2002-01-05') FROM emp;
This returns 1.
|
|
SECOND(time_exp)
|
Returns the second based on the second field in time_exp as an integer value in the range of 0–59. For example:
SELECT SECOND('22:21:20') FROM emp;
This returns 20.
|
|
WEEK(date_exp)
|
Returns the week of the year based on the week field in date_exp as an integer value in the range of 1–53. For example:
SELECT WEEK('2002-01-05') FROM emp;
This returns 1.
|
|
YEAR(date_exp)
|
Returns the year based on the year field in date_exp as an integer value. For example:
SELECT YEAR('2002-01-01') FROM emp;
This returns 2002.
|