TechNet
Export (0) Print
Expand All

Data Type Support for ODBC Date and Time Improvements

 

Applies To: SQL Server 2016 Preview

This topic provides information about ODBC types that support SQL Server date and time data types.

In addition to the ODBC data types (SQL_TYPE_TIMESTAMP and SQL_TIMESTAMP), two new data types were added in SQL Server Native Client ODBC to expose the new server types:

  • SQL_SS_TIME2

  • SQL_SS_TIMESTAMPOFFSET

The following table shows the complete server-type mapping. Notice that some cells of the table contain two entries; in these cases, the first is the ODBC 3.0 value and the second is the ODBC 2.0 value.

SQL Server data typeSQL data typeValue
DatetimeSQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
93 (sql.h)

11 (sqlext.h)
SmalldatetimeSQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
93 (sql.h)

11 (sqlext.h)
DateSQL_TYPE_DATE

SQL_DATE
91 (sql.h)

9 (sqlext.h)
TimeSQL_SS_TIME2-154 (SQLNCLI.h)
DatetimeOFFSETSQL_SS_TIMESTAMPOFFSET-155 (SQLNCLI.h)
Datetime2SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
93 (sql.h)

11 (sqlext.h)

The following table lists the corresponding structures and ODBC C types. Because ODBC does not permit driver defined C types, SQL_C_BINARY is used for time and datetimeoffset as binary structures.

SQL data typeMemory layoutDefault C data typeValue (sqlext.h)
SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
SQL_TIMESTAMP_STRUCT

TIMESTAMP_STRUCT
SQL_C_TYPE_TIMESTAMP

SQL_C_TIMESTAMP
SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
SQL_TYPE_DATE

SQL_DATE
SQL_DATE_STRUCT

DATE_STRUCT
SQL_C_TYPE_DATE

SQL_C_DATE
SQL_TYPE_DATE

SQL_DATE
SQL_SS_TIME2SQL_SS_TIME2_STRUCTSQL_C_SS_TIME2

SQL_C_BINARY (ODBC 3.5 and earlier)
0x4000 (sqlncli.h)

SQL_BINARY (-2)
SQL_SS_TIMESTAMPOFFSETSQL_SS_TIMESTAMPOFFSET_STRUCTSQL_C_SS_TIMESTAMPOFFSET

SQL_C_BINARY (ODBC 3.5 and earlier)
0x4001 (sqlncli.h)

SQL_BINARY (-2)

When SQL_C_BINARY binding is specified, alignment checking will be performed and an error reported for incorrect alignment. The SQLSTATE for this error will be IM016, with the message "Incorrect structure alignment".

The following table shows the mappings between SQL Server data types, ODBC data types, and the ODBC string literals.

SQL Server data typeODBC data typeString format for client conversions
DatetimeSQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
'yyyy-mm-dd hh:mm:ss[.999]'

 SQL Server supports up to three fractional second digits for Datetime.
SmalldatetimeSQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
'yyyy-mm-dd hh:hh:ss'

This data type has an accuracy of one minute. The seconds component will be zero on output and will be rounded by the server on input.
DateSQL_TYPE_DATE

SQL_DATE
'yyyy-mm-dd'
TimeSQL_SS_TIME2'hh:mm:ss[.9999999]'

Fractional seconds can optionally be specified using up to seven digits.
Datetime2SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP
'yyyy-mm-dd hh:mm:ss[.9999999]'

Fractional seconds can optionally be specified using up to seven digits.
DatetimeOFFSETSQL_SS_TIMESTAMPOFFSET'yyyy-mm-dd hh:mm:ss[.9999999] +/- hh:mm'

Fractional seconds can optionally be specified using up to seven digits.

There are no changes to the ODBC escape sequences for date/time literals.

Fractional seconds in results always use a dot (.), rather than a colon (:).

String values returned to applications are always the same length for a given column. Year, month, day, hour, minute, and second components are padded with leading zeros to their maximum width, and there is one space between date and time in datetime values. There is also one space between the time and timezone offset in a datetimeoffset value. A timezone offset is always preceded by a sign; when the offset is zero, this sign is a plus (+). Fractional seconds are padded with trailing zeros if necessary, up to the defined precision for the column. For datetime columns, there are three fractional seconds digits. For smalldatetime columns, there are no fractional seconds' digits, and the seconds will always be zero.

An empty string is not a valid date/time literal and it does not represent a NULL value. An attempt to convert an empty string to a date/time value will result in SQLState 22018 error and the message "Invalid character value for cast specification".

Conversions from string parameters will expect strings in the same format, with the exceptions that the sign of a timezone with zero hours and zero minutes can be either plus or minus, and trailing zeros are allowed for fractional seconds up to a maximum of 9 digits. A time component can terminate with a decimal point and no fractional seconds digits.

Currently, the driver allows additional white space around punctuation characters and the space between time and timezone offset is optional. However, this might change in a future release; applications should not rely on the current behavior.

In the structures described below, ODBC specifies the following constraints, which are taken from the Gregorian calendar:

  • Month range is 1 through 12.

  • Day field range is 1 through the number of days in the month, and must be consistent with year and month fields, taking account of leap years.

  • Hour range is 0 through 23.

  • Minute range is 0 through 59.

  • Seconds range is 0 through 61.9(n). This allows up to two leap seconds to maintain synchronization with sideral time.

    Note that SQL Server does not allow leap seconds, so second values greater than 59 will cause a server error.

Implementations for the following existing ODBC structs have been modified to support the new SQL Server date and time data types. The definitions, however, have not changed.

  • DATE_STRUCT

  • TIME_STRUCT

  • TIMESTAMP_STRUCT

There are also two new structs:

  • SQL_SS_TIME2_STRUCT

  • SQL_SS_TIMESTAMPOFFSET_STRUCT

SQL_SS_TIME2_STRUCT

This struct is padded to 12 bytes on both 32-bit and 64-bit operating systems.

typedef struct tagSS_TIME2_STRUCT {  
   SQLUSMALLINT hour;  
   SQLUSMALLINT minute;  
   SQLUSMALLINT second;  
   SQLUINTEGER fraction;  
} SQL_SS_TIME2_STRUCT;  

SQL_SS_TIMESTAMPOFFSET_STRUCT

typedef struct tagSS_TIMESTAMPOFFSET_STRUCT {  
   SQLSMALLINT year;  
   SQLUSMALLINT month;  
   SQLUSMALLINT day;  
   SQLUSMALLINT hour;  
   SQLUSMALLINT minute;  
   SQLUSMALLINT second;  
   SQLUINTEGER fraction;  
   SQLSMALLINT timezone_hour;  
   SQLSMALLINT timezone_minute;  
} SQL_SS_TIMESTAMPOFFSET_STRUCT;  

If the timezone_hour is negative, the timezone_minute must be negative or zero. If the timezone_hour is positive, the timezone_minute must be positive or zero. If the timezone_hour is zero, the stimezone_minute may have any value in the range -59 through +59.

Date and Time Improvements (ODBC)

Community Additions

ADD
Show:
© 2016 Microsoft