solidDB Help : solidDB reference : ODBC: Scalar functions : Time and date functions
  
Time and date functions
This section lists time and date functions that are included in the ODBC scalar function set.
Applications can call SQLGetInfo with the SQL_TIMEDATE_FUNCTIONS information type to determine which time and date functions are supported by a driver.
Arguments
The following table details the time and date function argument types:
 
Argument type
Definition
timestamp_exp
These arguments 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.
date_exp
These arguments 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.
time_exp
These arguments 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
Function list
The following table details the time and date functions:
 
Function
Description
CURRENTTIME [(timejprecision)]
(ODBC 3.0)
Returns the current local time as a time value. The time_precision argument (0-6) determines the milliseconds precision of the returned value.
Value 0 means no timestamp or time fractions are shown. If the value is not specified, the value 0 is used.
CURRENT_TIMESTAMP [(timestamp_precision)]
(ODBC 3.0)
Returns the current local date and local time as a timestamp value. The timestamp_precision argument (0-6) determines the milliseconds precision of the returned timestamp.
Value 0 means no timestamp or time fractions are shown. If the value is not specified, the value 0 is used.
CURDATE( )
(ODBC 1.0)
Returns the current date.
CURTIME[(timejprecision)]
(ODBC 1.0)
Returns the current local time. The time_precision argument (0-6) determines the milliseconds precision of the returned value.
Value 0 means no timestamp or time fractions are shown. If the value is not specified, the value 0 is used.
DAYNAME(date_exp)
(ODBC 2.0)
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)
(ODBC 1.0)
Returns the day of the month in date_exp as an integer value in the range of 1-31.
DAYOFWEEK(date_exp)
(ODBC 1.0)
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)
(ODBC 1.0)
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 FROM extract_source)
(ODBC 3.0)
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
MONTH
DAY
HOUR
MINUTE
SECOND
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)
(ODBC 1.0)
Returns the hour based on the hour field in time_exp as an integer value in the range of 0-23.
MINUTE(time_exp)
(ODBC 1.0)
Returns the minute based on the minute field in time_exp as an integer value in the range of 0-59.
MONTH(date_exp)
(ODBC 1.0)
Returns the month based on the month field in date_exp as an integer value in the range of 1-12.
MONTHNAME(date_exp)
(ODBC 2.0)
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 [(timestamp_precision)]
(ODBC 1.0)
Returns current date and time as a timestamp value. The timestamp_precision argument (0-6) determines the milliseconds precision of the returned timestamp.
Value 0 means no timestamp or time fractions are shown. If the value is not specified, the value 0 is used.
QUARTER(date_exp)
(ODBC 1.0)
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)
(ODBC 1.0)
Returns the second 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:
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
where fractional seconds are expressed in billionths of a second (nanoseconds). For example, the following SQL statement returns the name of each employee and their one-year anniversary date:
SELECT NAME, {fn
TIMESTAMPADD(SQL_TSI_YEAR, 1,
HIRE_DATE)} FROM
EMPLOYEES
If timestamp_exp is a time value and interval specifies day, 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.
An application determines which intervals a data source supports by calling SQLGetInfo with the SQL_TIMEDATE_ADD_INTERVALS option.
Go up to
ODBC: Scalar functions