Data Type Support for ODBC Date/Time Improvements

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

Data Type Mapping in Parameters and Resultsets

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.

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.

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".

Data Formats: Strings and Literals

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

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.

Data Formats: Data Structures

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.

See Also

Concepts

Date/Time Improvements (ODBC)