Application Note for QuNect ODBC for Quick Base

Scalar Date and Time Functions Supported by QuNect ODBC for Quick Base

Arguments denoted as *timestamp_exp* can be the name of a column, the result of another scalar function, or an *ODBC-time-escape*, *ODBC-date- escape*, or *ODBC-timestamp-escape*, where the underlying data type could be represented as SQL_CHAR, SQL_VARCHAR, SQL_TYPE_TIME, SQL_TYPE_DATE, or SQL_TYPE_TIMESTAMP.

Arguments denoted as *date_exp* can be the name of a column, the result of another scalar function, or an *ODBC-date- escape* or *ODBC-timestamp-escape*, where the underlying data type could be represented as SQL_CHAR, SQL_VARCHAR, SQL_TYPE_DATE, or SQL_TYPE_TIMESTAMP.

Arguments denoted as *time_exp* can be the name of a column, the result of another scalar function, or an *ODBC-time-escape* or *ODBC-timestamp-escape*, where the underlying data type could be represented as SQL_CHAR, SQL_VARCHAR, SQL_TYPE_TIME, or SQL_TYPE_TIMESTAMP.

The CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP timedate scalar functions have been added in ODBC 3.0 to align with SQL-92.

Function | Description |
---|---|

{fn CURRENT_DATE( )} |
Returns the current date. |

{fn CURRENT_TIME([time-precision])} |
Returns the current local time. The time-precision argument determines the seconds precision of the returned value. |

{fn 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. |

{fn CURDATE( )} |
Returns the current date. |

{fn CURTIME( )} |
Returns the current local time. |

{fn 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. |

{fn 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. |

{fn 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. |

{fn 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. |

{fn EXTRACT(extract-field FROM 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:
YEAR 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 |

{fn HOUR(time_exp)} |
Returns the hour based on the hour field in time_exp as an integer value in the range of 0–23. |

{fn MINUTE(time_exp)} |
Returns the minute based on the minute field in time_exp as an integer value in the range of 0–59. |

{fn MONTH(date_exp)} |
Returns the month based on the month field in date_exp as an integer value in the range of 1–12. |

{fn 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. |

{fn NOW( )} |
Returns current date and time as a timestamp value. |

{fn 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. |

{fn SECOND(time_exp)} |
Returns the second based on the second field in time_exp as an integer value in the range of 0–59.
Returns the timestamp calculated by adding SQL_TSI_FRAC_SECOND 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 If If An application determines which intervals a data source supports by calling |

{fn TIMESTAMPADD(interval, integer-exp, timestamp_exp)} |
Returns a timestamp that is the number of intervals of type interval greater or less than timestamp_exp depending on whether integer-exp is positive or negative respectively. Valid values of interval are the following keywords:
SQL_TSI_FRAC_SECOND where fractional seconds are expressed in billionths of a second. For example, the following SQL statement returns records more than 60 days old: SELECT "Date Created" FROM "People bcg9m2wqw" WHERE "Date Created" < {fn TIMESTAMPADD(SQL_TSI_DAY, -60, {fn CURRENT_TIMESTAMP()})} An application determines which intervals a data source supports by calling |

{fn 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:
SQL_TSI_FRAC_SECOND 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 If either timestamp expression is a time value and If either timestamp expression is a date value and An application determines which intervals a data source supports by calling |

{fn 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. |

{fn YEAR(date_exp)} |
Returns the year based on the year field in date_exp as an integer value. The range is data source–dependent. |