Access911.net   |   a9BBS   |   OTaA System  
  搜索文章:  
Access911欢迎您光临  
   主页      上传      繁體版       论坛     
设为首页  |  加入收藏   
  
你现在的位置:文章索引 -> 文章分类 -> 函数  
 首页|  近日更新|  下载  |  文章索引  |  搜索|  术语|  承接工程|  
 
系统正在加载内容,请耐心等待...
 
 查询
 窗体
 报表
 
 
 VBA
 函数
 ADO/DAO/ADO.NET
 API
 ADP
 安全
 发布
 OA
 ASP/ASP.NET
 其他语言
 控件
 DELPHI
 C#/.Net
 本站
 其他
 小例程
 常用软件
 参考文档
 业主作品
 网友大作
 
 
友情链接
 access911.net
 
访问人次
 1702237
 
站长 E-Mail
 net911@sina.com
 access911@gmail.com
 
RSS 订阅

显示附加信息 >>>

ODBC标量函数(3)(英文)——日期时间函数

作者:MS  摘自:Microsoft  :cg1  更新日期:2004-7-13  浏览人次:

 

Time, Date, and Interval Functions

The following table lists time and date functions that are included in the ODBC scalar function set. An application can determine which time and date functions are supported by a driver by calling SQLGetInfo with an information type of SQL_TIMEDATE_FUNCTIONS.

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
CURRENT_DATE( )
(ODBC 3.0)
Returns the current date.
CURRENT_TIME[(time-precision)]
(ODBC 3.0)
Returns the current local time. The time-precision argument determines the seconds precision of the returned value.
CURRENT_TIMESTAMP
[(
timestamp-precision)]
(ODBC 3.0)
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( )
(ODBC 1.0)
Returns the current date.
CURTIME( )
(ODBC 1.0)
Returns the current local time.
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 based on the month field 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( )
(ODBC 1.0)
Returns current date and time as a timestamp value.
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 based on the second field in time_exp as an integer value in the range of 0–59.

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. For example, the following SQL statement returns the name of each employee and his or her 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 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.

An application determines which intervals a data source supports by calling SQLGetInfo with the SQL_TIMEDATE_ADD_INTERVALS option.

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)
(ODBC 2.0)
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
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. 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(SQL_TSI_YEAR,
{fn CURDATE()}, HIRE_DATE)}
FROM EMPLOYEES

If either 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 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.

An application determines which intervals a data source supports by calling SQLGetInfo with the SQL_TIMEDATE_DIFF_INTERVALS option.

WEEK(date_exp)
(ODBC 1.0)
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)
(ODBC 1.0)
Returns the year based on the year field in date_exp as an integer value. The range is data source–dependent.

 

 
相关文章
     没有手动相关文章
     ODBC标量函数(6)(英文)——SQL 92 CAST 函数
     ODBC标量函数(5)(英文)——数据类型转换函数
     ODBC标量函数(4)(英文)——系统函数
     ODBC标量函数(2)(英文)——数值函数
     ODBC标量函数(1)(英文)——字符串
 
评论
     查看或发表更多的评论,请单击这里。
 
 
 
 
 
   
  Access911.net   |   a9BBS   |   OTaA System   |
建站日期:2000年4月2日  |  设计施工:陈格 ( access911 & cg1 )
 Copyright © 2000 - 2003 COMET, 陈格 保留所有权利