Bulk Copy Changes for Enhanced Date/Time Types (OLE DB and ODBC)
This topic describes the date/time enhancements to support bulk copy functionality. The information in this topic is valid for both OLE DB and ODBC in SQL Server Native Client.
Format Files
When building format files interactively, the following table describes the input used to specify date/time types and the corresponding host-file data type names.
File storage type |
Host file data type |
Response to the prompt: "Enter the file storage type of field <field_name> [<default>]:" |
|---|---|---|
Datetime |
SQLDATETIME |
d |
Smalldatetime |
SQLDATETIM4 |
D |
Date |
SQLDATE |
de |
Time |
SQLTIME |
te |
Datetime2 |
SQLDATETIME2 |
d2 |
Datetimeoffset |
SQLDATETIMEOFFSET |
do |
The XML format file XSD will have the following additions:
<xs:complexType name="SQLDATETIME2">
<xs:complexContent>
<xs:extension base="bl:Fixed"/>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="SQLDATETIMEOFFSET">
<xs:complexContent>
<xs:extension base="bl:Fixed"/>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="SQLDATE">
<xs:complexContent>
<xs:extension base="bl:Fixed"/>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="SQLTIME">
<xs:complexContent>
<xs:extension base="bl:Fixed"/>
</xs:complexContent>
</xs:complexType>
Character Data Files
In character data files, date and time values are represented as described in the "Data Formats: Strings and Literals" section of Data Type Support for ODBC Date/Time Improvements for ODBC, or of Data Type Support for OLE DB Date/Time Improvements for OLE DB.
In native data fles, date and time values for the four new types are represented as their TDS representations with a scale of 7 (because this is the maximum supported by SQL Server and bcp data files do not store the scale of these columns). There is no change to the storage of the existing datetime and smalldatetime type or their tabular data stream (TDS) representations.
The storage sizes for the different storage types are as follows for OLE DB:
File storage type |
Storage size in bytes |
|---|---|
datetime |
8 |
smalldatetime |
4 |
date |
3 |
time |
6 |
datetime2 |
9 |
datetimeoffset |
11 |
The sizes are as follows for ODBC. Note that it is not necessary to store precision in either format or data files, because BCP.exe will always retrieve precision from the server.
File storage type |
Storage size in bytes |
Storage format |
|---|---|---|
datetime (d) |
8 |
TDS |
smalldatetime (D) |
4 |
TDS |
date (de) |
3 |
TDS |
time (te) |
6 |
TDS |
datetime2 (d2) |
9 |
TDS |
datetimeoffset (do) |
11 |
TDS |
BCP Types in sqlncli.h
The following types are defined in sqlncli.h to be used with the BCP API extensions to ODBC. These types are passed with the eUserDataType parameter of IBCPSession::BCPColFmt in OLE DB.
File storage type |
Host file data type |
Type in sqlncli.h for use with IBCPSession::BCPColFmt |
Value |
|---|---|---|---|
Datetime |
SQLDATETIME |
BCP_TYPE_SQLDATETIME |
0x3d |
Smalldatetime |
SQLDATETIM4 |
BCP_TYPE_SQLDATETIME4 |
0x3a |
Date |
SQLDATE |
BCP_TYPE_SQLDATE |
0x28 |
Time |
SQLTIME |
BCP_TYPE_SQLTIME |
0x29 |
Datetime2 |
SQLDATETIME2 |
BCP_TYPE_SQLDATETIME2 |
0x2a |
Datetimeoffset |
SQLDATETIMEOFFSET |
BCP_TYPE_SQLDATETIMEOFFSET |
0x2b |
BCP Data Type Conversions
The following tables provide conversion information.
OLE DB Note The following conversions are performed by IBCPSession. IRowsetFastLoad uses OLE DB conversions as defined in Conversions Performed from Client to Server. Note that datetime values are rounded to 1/300th of a second and smalldatetime values have seconds set to zero after client conversions described below have been performed. Datetime rounding propagates through hours and minutes, but not the date.
To --> From |
date |
time |
smalldatetime |
datetime |
datetime2 |
datetimeoffset |
char |
wchar |
|---|---|---|---|---|---|---|---|---|
Date |
1 |
- |
1,6 |
1,6 |
1,6 |
1,5,6 |
1,3 |
1,3 |
Time |
N/A |
1,10 |
1,7,10 |
1,7,10 |
1,7,10 |
1,5,7,10 |
1,3 |
1,3 |
Smalldatetime |
1,2 |
1,4,10 |
1 |
1 |
1,10 |
1,5,10 |
1,11 |
1,11 |
Datetime |
1,2 |
1,4,10 |
1,12 |
1 |
1,10 |
1,5,10 |
1,11 |
1,11 |
Datetime2 |
1,2 |
1,4,10 |
1,10 (ODBC)1,12 (OLE DB) |
1,10 |
1,10 |
1,5,10 |
1,3 |
1,3 |
Datetimeoffset |
1,2,8 |
1,4,8,10 |
1,8,10 |
1,8,10 |
1,8,10 |
1,10 |
1,3 |
1,3 |
Char/wchar (date) |
9 |
- |
9,6 (ODBC)9,6,12 (OLE DB) |
9,6 (ODBC)9,6,12 (OLE DB) |
9,6 |
9,5,6 |
N/A |
N/A |
Char/wchar (time) |
- |
9,10 |
9,7,10 (ODBC)9,7,10,12 (OLE DB) |
9,7,10 (ODBC)9,7,10, 12 (OLE DB) |
9,7,10 |
9,5,7,10 |
N/A |
N/A |
Char/wchar (datetime) |
9,2 |
9,4,10 |
9,10 (ODBC)9,10,12 (OLE DB) |
9,10 (ODBC)9,10,12 (OLE DB) |
9,10 |
9,5,10 |
N/A |
N/A |
Char/wchar (datetimeoffset) |
9,2,8 |
9,4,8,10 |
9,8,10 (ODBC)9,8,10,12 (OLE DB) |
9,8,10 (ODBC)9,8,10,12 (OLE DB) |
9,8,10 |
9,10 |
N/A |
N/A |
Key to Symbols
Symbol |
Meaning |
||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
- |
No conversion is supported. An ODBC diagnostic record is generated with SQLSTATE 07006 and the message "Restricted data type attribute violation". |
||||||||||||||||||||||||||||||||
1 |
If the data supplied is not valid, an ODBC diagnostic record is generated with SQLSTATE 22007 and the message "Invalid datetime format". For datetimeoffset values, the time portion must be within range after conversion to UTC, even if no conversion to UTC is requested. This is because TDS and the server always normalize the time in datetimeoffset values for UTC. So the client must check that time components are within the range supported after conversion to UTC. |
||||||||||||||||||||||||||||||||
2 |
The time component is ignored. |
||||||||||||||||||||||||||||||||
3 |
For ODBC, If truncation with data loss occurs, a diagnostic record is generated with SQLSTATE 22001 and message 'String data, right truncated' The number of fractional seconds digits (the scale) is determined from the destination column’s size according to the following table. For column sizes larger than the range in the table, a scale of 7 is implied. This conversion should allow for up to nine fractional second digits, the maximum allowed by ODBC.
For OLE DB, if truncation with data loss occurs, an error is posted. For datetime2, the number of fractional seconds digits (the scale) is determined from the destination column’s size according to the following table. For column sizes larger than the range in the table a scale of 9 is implied. This conversion should allow for up to nine fractional second digits, the maximum allowed by OLE DB.
|
||||||||||||||||||||||||||||||||
4 |
The date component is ignored. |
||||||||||||||||||||||||||||||||
5 |
The timezone is set to UTC (for example, 00:00). |
||||||||||||||||||||||||||||||||
6 |
The time is set to zero. |
||||||||||||||||||||||||||||||||
7 |
The date is set to 1900-01-01. |
||||||||||||||||||||||||||||||||
8 |
The timezone offset is ignored. |
||||||||||||||||||||||||||||||||
9 |
The string is parsed and converted to a date, datetime, datetimeoffset, or time value, depending on the first punctuation character encountered and presence of remaining components. The string is then converted to the target type, following the rules in the table at the end of this topic for the source type discovered by this process. If the data supplied cannot be parsed without error, or if any component part is outside the range allowed, or if there is no conversion from the literal type to target type, an error is posted (OLE DB) or an ODBC diagnostic record is generated with SQLSTATE 22018 and the message "Invalid character value for cast specification". For datetime and smalldatetime parameters, if the year is outside the range these types support, an error is posted (OLE DB) or an ODBC diagnostic record is generated with SQLSATE 22007 and the message "Invalid datetime format". For datetimeoffset, the value must be within range after conversion to UTC, even if no conversion to UTC is requested. This is because TDS and the server always normalize the time in datetimeoffset values for UTC, so the client must verify that time components are within the range supported after conversion to UTC. If the value is not within the supported UTC range, an error is posted (OLE DB), or an ODBC diagnostic record is generated with SQLSTATE 22007 and the message "Invalid datetime format". |
||||||||||||||||||||||||||||||||
10 |
If truncation with data loss occurs on a client to server conversion, an error is posted (OLE DB) or an ODBC diagnostic record is generated with SQLSTATE 22008 and the message "Datetime field overflow". This error also occurs if the value falls outside the range that can be represented by the UTC range used by the server. If seconds or fractional seconds truncation occurs in a server to client conversion, there is only a warning. |
||||||||||||||||||||||||||||||||
11 |
If truncation with data loss occurs, a diagnostic record is generated. On a server-to-client conversion, this is a warning (ODBC SQLSTATE S1000). On a client-to-server conversion, this is an error (ODBC SQLSTATE 22001). |
||||||||||||||||||||||||||||||||
12 |
Seconds are set to zero and fractional seconds are discarded. No truncation error is possible. |
||||||||||||||||||||||||||||||||
N/A |
Existing SQL Server 2005 and earlier behavior is maintained. |