Skip to content

Latest commit

 

History

History
47 lines (32 loc) · 3.22 KB

date-and-time-improvements.md

File metadata and controls

47 lines (32 loc) · 3.22 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic
Date and Time Improvements
Learn about the OLE DB Driver for SQL Server support for the date and time data types that were added in SQL Server 2008.
David-Engel
davidengel
06/12/2018
sql
connectivity
reference

Date and Time Improvements

[!INCLUDE SQL Server]

[!INCLUDEDriver_OLEDB_Download]

This topic describes the OLE DB Driver for SQL Server support for the date and time data types that were added in [!INCLUDEsql2008-md].

For more information about date/time improvements, see Date and Time Improvements (OLE DB).

Usage

The following sections describe various ways of using the new date and time types.

Use Date as a Distinct Data Type

Beginning with [!INCLUDEsql2008-md], enhanced support for date/time types makes it more efficient to use the DBTYPE_DBDATE OLE DB type.

Use Time as a Distinct Data Type

OLE DB already has a data type that just contains the time, DBTYPE_DBTIME, which has a precision of 1 second.

The new [!INCLUDEssNoVersion] time data type has fractional seconds accurate to 100 nanoseconds. This requires a new type in OLE DB Driver for SQL Server: DBTYPE_DBTIME2. Existing applications written to use times with no fractional seconds can use time(0) columns. The existing OLE DB DBTYPE_TIME type and its corresponding structs should work correctly, unless the applications rely on the type returned in metadata.

Use Time as a Distinct Data Type with Extended Fractional Seconds Precision

Some applications, such as process control and manufacturing applications, require the ability to handle time data with a precision of up to 100 nanoseconds. New type for this purpose in OLE DB is DBTYPE_DBTIME2.

Use Datetime with Extended Fractional Seconds Precision

OLE DB already defines a type with a precision of up to 1 nanosecond. However, this type is already used by existing [!INCLUDEssNoVersion] applications and such applications have an expectation of only 1/300 of a second precision. The new datetime2(3) type is not directly compatible with the existing datetime type. If there is a risk that this will affect application behavior, applications must use a new DBCOLUMN flag to determine the actual server type.

Use Datetime with Extended Fractional Seconds Precision and Timezone

Some applications require datetime values with timezone information. This is supported by the new DBTYPE_DBTIMESTAMPOFFSET type.

Use Date/Time/Datetime/Datetimeoffset Data with Client-Side Conversions Consistent with Existing Conversions

The conversions are extended in a consistent manner to include conversions between all date and time types introduced in [!INCLUDEsql2008-md].

See Also

OLE DB Driver for SQL Server Features