Bindings and Conversions (OLE DB)

This section discusses how to convert between datetime and datetimeoffset values. The conversions described in this section are either already provided by OLE DB or are a consistent extension of OLE DB.

The format of literals and strings for dates and times in OLE DB generally follows ISO, and is not dependent on the client locale. One exception is DBTYPE_DATE, where the standard is OLE Automation. However, because SQL Server Native Client only converts between types when data is transmitted to or from the client, there is no way for an application to force SQL Server Native Client to convert between DBTYPE_DATE and string formats. Otherwise, strings use the following formats (text in brackets indicates an optional element):

• The format of datetime and datetimeoffset strings is:

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

• The format of time strings is:

  hh:mm:ss[.9999999]

• The format of date strings is:

  yyyy-mm-dd

Conversions from strings allow flexibility in white space and field width. For more information, see the "Data Formats: Strings and Literals" section in Data Type Support for OLE DB Date/Time Improvements.

The following are general conversion rules:

• When a string is converted to a date/time type, the string is first parsed as an ISO literal. If this fails, the string is parsed as an OLE date literal, which has time components.

• If no time is present but the receiver can store time, the time is set to zero. If no date is present but the receiver can store a date, the date is set to the current date when ISO conversions are used and to 1899-12-30 when OLE conversions are used.

• If no timezone is present in the data type that the client is using, but the server can store timezone, the data on the client is assumed to be in the client timezone.

• If no timezone is present at the server but the client has timezone information, the UTC timezone is assumed. This differs from server behavior.

• If the time is present but the receiver cannot store time, the time component is ignored.

• If the date is present but the receiver cannot store the date, the date component is ignored.

• If truncation of seconds or fractional seconds occurs when converting from client to server, DB_E_ERRORSOCCURRED is returned and the status DBSTATUS_E_DATAOVERFLOW is set.

• If truncation of seconds or fractional seconds occurs when converting from server to client, DBSTATUS_S_TRUNCATED is set

In This Section

• Conversions Performed from Client to Server
  Describes date/time conversions performed between a client application written with SQL Server Native Client OLE DB and SQL Server 2008 (or later).

• Conversions Performed from Server to Client
  Describes date/time conversions performed between SQL Server 2008 (or later) and a client application written with SQL Server Native Client OLE DB.

See Also

Concepts

Date/Time Improvements (OLE DB)