Date and Time Macro Functions (FireDAC)
Go Up to Preprocessing Command Text (FireDAC)
The following table lists the date and time macro functions:
Function | Description | Local expression engine |
---|---|---|
CURRENT_DATE( ) | Returns the current date. | + |
CURRENT_TIME[(time-precision)] | Returns the current local time. The time-precision argument determines the seconds precision of the returned value. | + |
CURRENT_TIMESTAMP [(timestamp-precision)] | Returns the current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp. | + |
CURDATE( ) | Returns the current date. | + |
CURTIME( ) | Returns the current local time. | + |
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. | + |
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. | + |
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. | + |
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 datetime or interval expression. The extract-field argument can be one of the following keywords:
The precision of the returned value is implementation-defined. The scale is 0 unless SECOND is specified, in which case the scale is not less than the fractional seconds precision of the extract-source field. |
+ |
HOUR(time_exp) | Returns the hour based on the hour field in time_exp as an integer value in the range of 0–23. | + |
MINUTE(time_exp) | Returns the minute based on the minute field in time_exp as an integer value in the range of 0–59. | + |
MONTH(date_exp) | Returns the month based on the month field in date_exp as an integer value in the range of 1–12. | + |
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. | + |
NOW( ) | Returns the current date and time as a timestamp value. | + |
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. | + |
SECOND(time_exp) | Returns the second based on the second field in time_exp as an integer value in the range of 0–59. | + |
TIMESTAMPADD(interval, integer_exp, timestamp_exp) | Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords:
where fractional seconds are expressed in billionths of a second. For example, the following SQL statement returns the name of each employee and his or her one-year anniversary date: SELECT NAME, {fn TIMESTAMPADD(‘YEAR’, 1, HIRE_DATE)} FROM EMPLOYEES If timestamp_exp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp. If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp. |
+ |
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2) | Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords:
where fractional seconds are expressed in billionths of a second. For example, the following SQL statement returns the name of each employee and the number of years he or she has been employed: SELECT NAME, {fn TIMESTAMPDIFF(‘YEAR’, {fn CURDATE()}, HIRE_DATE)} FROM EMPLOYEES. If either the timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps. If either the timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps. |
+ |
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. | + |
YEAR(date_exp) | Returns the year based on the year field in date_exp as an integer value. The range is data source–dependent. | + |
(*) Some DBMSs fail to work with FRAC_SECOND.