日期、时间和时间戳文本Date, Time, and Timestamp Literals

日期、时间和时间戳文本的转义序列是The escape sequence for date, time, and timestamp literals is

{ -type " value "}{ -type ' value '}

其中,原义类型是下表中列出的值之一。where literal-type is one of the values listed in the following table.

文本类型literal-type 含义Meaning 的格式Format of value
dd 日期Date yyyy-mmmm-ddyyyy-mm-dd
关心t 阶段Time* hhmmss[1]hh:mm:ss[1]
tsts 时间戳Timestamp yyyy-mmmm-dd hhmmss[。f ...]2yyyy-mm-dd hh:mm:ss[.f...][1]

[1] 时间或时间间隔文本中小数点右边的位数,其中包含秒部分,它取决于 SQL_DESC_PRECISION 描述符字段中包含的秒精度。[1] The number of digits to the right of the decimal point in a time or timestamp interval literal containing a seconds component is dependent on the seconds precision, as contained in the SQL_DESC_PRECISION descriptor field. (有关详细信息,请参阅SQLSetDescField。)(For more information, see SQLSetDescField.)

有关日期、时间和时间戳转义序列的详细信息,请参阅附录 C: SQL 语法中的日期、时间和时间戳转义序列For more information about the date, time, and timestamp escape sequences, see Date, Time, and Timestamp Escape Sequences in Appendix C: SQL Grammar.

例如,以下两个 SQL 语句均更新 Orders 表中销售订单1023的打开日期。For example, both of the following SQL statements update the open date of sales order 1023 in the Orders table. 第一条语句使用转义序列语法。The first statement uses the escape sequence syntax. 第二个语句使用 "日期" 列的 Oracle Rdb 本机语法,它无法互操作。The second statement uses the Oracle Rdb native syntax for the DATE column and is not interoperable.

UPDATE Orders SET OpenDate={d '1995-01-15'} WHERE OrderID=1023  
UPDATE Orders SET OpenDate='15-Jan-1995' WHERE OrderID=1023  

日期、时间或时间戳文字的转义序列也可以放入绑定到日期、时间或时间戳参数的字符变量中。The escape sequence for a date, time, or timestamp literal also can be placed in a character variable bound to a date, time, or timestamp parameter. 例如,下面的代码使用绑定到字符变量的 date 参数来更新 Orders 表中销售订单1023的开放日期:For example, the following code uses a date parameter bound to a character variable to update the open date of sales order 1023 in the Orders table:

SQLCHAR      OpenDate[56]; // The size of a date literal is 55.  
SQLINTEGER   OpenDateLenOrInd = SQL_NTS;  
  
// Bind the parameter.  
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_TYPE_DATE, 0, 0,  
                  OpenDate, sizeof(OpenDate), &OpenDateLenOrInd);  
  
// Place the date in the OpenDate variable. In addition to the escape  
// sequence shown, it would also be possible to use either of the  
// strings "{d '1995-01-15'}" and "15-Jan-1995", although the latter  
// is data source-specific.  
strcpy_s( (char*) OpenDate, _countof(OpenDate), "{d '1995-01-15'}");  
  
// Execute the statement.  
SQLExecDirect(hstmt, "UPDATE Orders SET OpenDate=? WHERE OrderID = 1023", SQL_NTS);  

但是,将参数直接绑定到日期结构通常更高效:However, it is usually more efficient to bind the parameter directly to a date structure:

SQL_DATE_STRUCT   OpenDate;  
SQLINTEGER        OpenDateInd = 0;  
  
// Bind the parameter.  
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TYPE_DATE, 0, 0,  
                  &OpenDate, 0, &OpenDateLen);  
  
// Place the date in the dsOpenDate structure.  
OpenDate.year = 1995;  
OpenDate.month = 1;  
OpenDate.day = 15;  
  
// Execute the statement.  
SQLExecDirect(hstmt, "UPDATE Employee SET OpenDate=? WHERE OrderID = 1023", SQL_NTS);  

若要确定驱动程序是否支持日期、时间或时间戳文本的 ODBC 转义序列,应用程序将调用SQLGetTypeInfoTo determine whether a driver supports the ODBC escape sequences for date, time, or timestamp literals, an application calls SQLGetTypeInfo. 如果数据源支持日期、时间或时间戳数据类型,它还必须支持相应的转义序列。If the data source supports a date, time, or timestamp data type, it must also support the corresponding escape sequence.

数据源还可以支持 ANSI SQL-92 规范中定义的 datetime 文本,它们不同于日期、时间或时间戳文本的 ODBC 转义序列。Data sources can also support the datetime literals defined in the ANSI SQL-92 specification, which are different from the ODBC escape sequences for date, time, or timestamp literals. 若要确定数据源是否支持 ANSI 文本,应用程序需要使用 SQL_ANSI_SQL_DATETIME_LITERALS 选项调用SQLGetInfoTo determine whether a data source supports the ANSI literals, an application calls SQLGetInfo with the SQL_ANSI_SQL_DATETIME_LITERALS option.

若要确定驱动程序是否支持用于间隔文本的 ODBC 转义序列,应用程序将调用SQLGetTypeInfoTo determine whether a driver supports the ODBC escape sequences for interval literals, an application calls SQLGetTypeInfo. 如果数据源支持日期时间间隔数据类型,它还必须支持相应的转义序列。If the data source supports a datetime interval data type, it must also support the corresponding escape sequence.

数据源还可以支持 ANSI SQL-92 规范中定义的 datetime 文本,它们不同于 datetime 间隔文本的 ODBC 转义序列。Data sources can also support the datetime literals defined in the ANSI SQL-92 specification, which are different from the ODBC escape sequences for datetime interval literals. 若要确定数据源是否支持 ANSI 文本,应用程序需要使用 SQL_ANSI_SQL_DATETIME_LITERALS 选项调用SQLGetInfoTo determine whether a data source supports the ANSI literals, an application calls SQLGetInfo with the SQL_ANSI_SQL_DATETIME_LITERALS option.