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 are required in SQL Server Native Client ODBC to expose the new server types:

  • SQL_SS_TIME2

  • SQL_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 type

SQL data type

Value

Datetime

SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP

93 (sql.h)

11 (sqlext.h)

Smalldatetime

SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP

93 (sql.h)

11 (sqlext.h)

Date

SQL_TYPE_DATE

SQL_DATE

91 (sql.h)

9 (sqlext.h)

Time

SQL_SS_TIME2

-154 (SQLNCLI.h)

DatetimeOFFSET

SQL_SS_TIMESTAMPOFFSET

-155 (SQLNCLI.h)

Datetime2

SQL_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 type

Memory layout

Default C data type

Value (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_TIME2

SQL_SS_TIME2_STRUCT

SQL_C_BINARY

SQL_BINARY (-2)

SQL_SS_TIMESTAMPOFFSET

SQL_SS_TIMESTAMPOFFSET_STRUCT

SQL_C_BINARY

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

Data Formats: Strings and Literals

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

SQL Server data type

ODBC data type

String format for client conversions

Datetime

SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP

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

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

Smalldatetime

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

Date

SQL_TYPE_DATE

SQL_DATE

'yyyy-mm-dd'

Time

SQL_SS_TIME2

'hh:mm:ss[.9999999]'

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

Datetime2

SQL_TYPE_TIMESTAMP

SQL_TIMESTAMP

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

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

DatetimeOFFSET

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

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