SQL Server

Introduction

The first hurdle a programmer faces when establishing connections to a database is deciding which type of connection and driver to use. As with MS Access, SQL Server presents the same problem: too many drivers and too many versions for each driver. In addition, Microsoft considers the drivers that come installed with the operating system to be deprecated. The Driver history for Microsoft SQL Server shows the history of various technologies used for connecting to SQL Server. On the page Driver feature support matrix for Microsoft SQL Server, you can see a list of the features supported by each driver.

On the other hand, there is an advantage over MS Access: to connect to SQL Server, no driver installation is required, as .NET contains its own, in fact, it has two drivers, and one of them is included with Excel To Database.

The tests detailed here for SQL Server do not include tests for "Microsoft SQL Server Database File" (has anyone used that?) or tests with the Azure SQL Database cloud, as the MalaCharger team does not have a database in that environment due to limited financial resources.

Not all SQL Server drivers behave the same when facing corrupted data. Our exhaustive testing has identified scenarios where the driver fails without providing clear feedback, allowing Excel To Database to intercept these errors and provide precise diagnostics to the user.

We have stress-tested the engine with data type overflows, unexpected nulls, and connection drops. The goal is to ensure that, in the event of a server or driver failure, the application protects transaction integrity.

o facilitate precise debugging, Excel To Database captures the driver's native exception and exports it entirely in JSON format. This allows technicians to access the Status Code, the engine's error message, and extended details that are typically lost in simplified interfaces.

By including the Status Code within the JSON object, it is possible to quickly identify whether the failure is network-related, permission-based, or due to data integrity, allowing for an immediate response without the need to check internal SQL server logs.

Here is a summary of the data types supported by SQL Server, focused on the capabilities of each data type and its precision. For more details, you can check the page: Data types (Transact-SQL)

This chapter presents the results of different data provider behaviors and SQL Server drivers in various situations not covered in previous chapters. It aims to help resolve issues and questions regarding the behavior of Excel To Database.

SQL Server Drivers

• ODBC Drivers

  As stated in Driver history for Microsoft SQL Server (ODBC), Microsoft recommends using the Microsoft ODBC Driver for SQL Server, whose features and documentation can be found at: Microsoft ODBC Driver for SQL Server. On Release Notes for Microsoft ODBC Driver for SQL Server on Windows, in addition to finding links to download the driver, you can consult the features of each driver version. In general, it is recommended to read and study all the information Microsoft provides on SQL Server regarding ODBC.
  It is worth noting that the different options for building the connection string are listed at: Supported DSN/Connection String Keywords and Connection Attributes and at Using Connection String Keywords with SQL Server Native Client.

  On Microsoft SQL Server ODBC DSN Configuration, you can find information on how to configure the ODBC driver to generate the DSN files used in Excel To Database with ODBC connections.

  For testing, the ODBC Driver for SQL Server 18.4.1.1 (x64) in English is downloaded and installed, since attempting to install the Spanish version results in an error stating that the operating system is not supported (Windows 10 22H2 in Spanish and SQL Server 2019 Express in Spanish, both 64-bit). Is anyone surprised by this? Microsoft isn't.

  Finally, it should be noted that the .NET classes for handling these types of connections, System.Data.Odbc, are not updated and do not handle the data types returned by the drivers correctly. For example, the "Time" and "DateTimeOffset" data types from SQL Server are not recognized by these classes. An example of this is the loader provided with SQL Server, the "SQL Server Import and Export Wizard," which doesn't work for these data types with ODBC connections. However, with Excel To Database, we have made a significant effort to address this issue, so these data types can be loaded.

• OLE DB Drivers

  On Microsoft OLE DB Driver for SQL Server, it is stated that the drivers SQLOLEDB (Microsoft OLE DB for SQL Server) and SQLNCLI or SQLNCLI11 (SQL Server Native Client) are deprecated. The recommended driver is MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server) or MSOLEDBSQL19 (Microsoft OLE DB Driver 19 for SQL Server). In Release Notes for Microsoft OLE DB Driver for SQL Server, you can find download links for the driver as well as the features of each version of the driver. In general, it is recommended to read and study all the information that Microsoft has regarding SQL Server and OLE DB.
  It is worth highlighting that the different options for building the connection string can be found at: OLE DB Driver connection string keywords.

  For testing, the "Microsoft OLE DB Driver 19 for SQL Server" 64-bit version in Spanish is downloaded and installed. The driver can be downloaded from: Download Microsoft OLE DB Driver for SQL Server. It should be noted that for version 19.3.5, you need to obtain Microsoft Visual C++ Redistributable for Visual Studio 2015, 2017, 2019, and 2022 version 14.34 at a minimum. You can download it from: Microsoft Visual C++ Redistributable latest supported downloads. Please note that you need to install both the 64-bit and 32-bit redistributables even if you only wish to install the 64-bit driver.

  As with ODBC, the .NET classes for handling this type of connection, System.Data.OleDb, are not updated, and they do not properly handle the data types returned by the drivers. Thus, the SQL Server data types "Date", "Time", "DateTimeOffset", and "DateTime2" are mapped in .NET to strings, while the older data types "DateTime" and "SmallDateTime" are treated as date and time data types.

• Managed Drivers

  As can be seen in Driver feature support matrix for Microsoft SQL Server in the first table (for .NET drivers), there are two drivers: Microsoft.Data.SqlClient (the most recent one) and System.Data.SqlClient (the traditional one).
  In the current version of Excel To Database, the driver System.Data.SqlClient is used. In future versions, it is planned that the Microsoft.Data.SqlClient driver, which is more suitable, will also be supported.

  System.Data.SqlClient does not include connection resiliency features. However, Excel To Database includes appropriate code to handle specific exceptions and enable retries in certain cases.

  Information related to the System.Data.SqlClient driver can be found at: SQL Server and ADO.NET, and the different attributes that can be used in the connection string can be found at: SqlConnection.ConnectionString Property.

  It is also worth noting that with managed drivers, Excel To Database uses the SqlBulkCopy class during the data load process to speed it up: SqlBulkCopy Class. More information can be found at: Bulk Copy Operations in SQL Server.
  Transactions are used here, so if an error occurs during the data load, the transaction will be rolled back, and no data will be inserted. With ODBC and OLE DB data providers, the opposite occurs: the data is inserted row by row without any transaction, so if an error occurs in a specific row, all the previous rows' data will still be inserted. For these data providers, Excel To Database presents the option With Transaction.

Connection Options

Excel To Database enforces certain properties in the connection string for it to work properly:

* ".NET Data Provider for SQL Server" (Managed Driver):
    Persist Security Info=False;MultipleActiveResultSets=True;Application Name=\"Excel To Database\";Workstation ID=Nombre Máquina;ApplicationIntent=ReadWrite;Asynchronous Processing=True

* "OLE DB":
    Persist Security Info=False;MARS Connection=True;Application Name="Excel To Database";Workstation ID=Nombre Máquina;Application Intent=ReadWrite;DataTypeCompatibility=80

* "ODBC":
    MARS_Connection=Yes;app=Excel To Database;wsid=Nombre Máquina;ApplicationIntent=ReadWrite
    For ODBC Driver 18 for SQL Server, the property is added: longasmax=Yes
  

Additionally, after the connection string is established, for OLE DB and ".NET Data Provider for SQL Server" Data Providers, Excel To Database displays relevant information regarding the efficiency of the connections in the Results Panel. For ODBC, this information is not displayed because, so far, a way to set this parameter in the connection string has not been found.

• PacketSize

  This parameter is largely unknown, even to Microsoft, as it usually doesn't provide much information regarding its utility or impact on the efficiency of database connections. Here, we present the useful information that has been found regarding this parameter, which is the same as what is shown in the "Results Panel":

    CONNECTION STRING

    Parameter: "PacketSize"

    The value of this parameter influences both network and SQL Server performance.
    Microsoft has several articles with recommendations for this parameter:
    http://microsoft-ssis.blogspot.com.es/2013/04/performance-best-practice-network.html
    and https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.packetsize.aspx

    In SQL Server it is configured as indicated in:
    https://msdn.microsoft.com/en-us/library/ms177437(v=sql.120).aspx

    The MTU value of the network should also be taken into account, so that the capacity of
    the network packets is fully used, avoiding inefficiencies. This value can be found
    by trying different values in the command:

    
      ping servername -f -l PacketSize
    


    Example:

    
      ping servername -f -l 1472
         Response from servername: bytes=1472 time=8ms TTL=120
      ping servername -f -l 1473
         The packet needs to be fragmented but DF was specified.
    

    NOTE: The ping command has an overhead of 28 bytes.

    So you could replace the default value of 8000 with 7500 (= (1472 + 28) * 5) and reconfigure
    SQL Server to support this packet size. Although, according to Microsoft, for "bulk copy"
    operations it is recommended that this value be as high as possible.

    It is also recommended to review the network configuration to allow/deny "jumbo mtu"
    frames in order to increase its performance.
    Example: https://en.wikipedia.org/wiki/Jumbo_frame
    although most of the time it is not advisable to enable them.

    Finally, note that this parameter can have a lot of influence on VPNs or satellite networks.

It is recommended to read: DBA Tactics for optimizing SQL Server Network Performance

Finally, after opening connections to SQL Server, Excel To Database sets the following options:

SET ANSI_DEFAULTS OFF;
SET ANSI_WARNINGS ON;
SET ANSI_PADDING ON;
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET CURSOR_CLOSE_ON_COMMIT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Books, Databases

The tests will be performed on Windows 10 64-bit, SQL Server 2019 Express with CU24, and Excel To Database compiled for .NET Framework 4.8 64-bit (System.Data.SqlClient). As expected, not all drivers or all possible options will be analyzed, but rather the most representative cases will be studied.

The latest versions of the drivers by default encrypt the connections, and they also require a server authentication certificate. As mentioned previously, this page is not a comprehensive guide to connection strings. So, when one of the following errors appear: Error "The certificate chain was issued by an authority that is not trusted" after updating SNAC applications or Error "The certificate received from the remote server was issued by a certification authority that is not trusted" when connecting to SQL Server. it will be resolved with one of the solutions mentioned at: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19#solutions (Use Encryption for Data=Optional/False; Encrypt=Optional/False; Trust Server Certificate=True).

• SQL Server Database

  A database with collation "Modern_Spanish_100_CS_AS", a "Simple" recovery model, no encryption, and the options SET ANSI_PADDING ON, SET ANSI_WARNINGS ON, SET ARITHABORT ON, SET NUMERIC_ROUNDABORT OFF along with other options will be used.

  The last two options are quite important for the result of the data load. In SET ARITHABORT (Transact-SQL) and SET NUMERIC_ROUNDABORT (Transact-SQL) the behavior of these options is described.
  It is worth noting that SQL Server has quite a bit of confusion when it comes to combining these options, and it is very complicated to choose the most convenient combination.

  Code

  9 Configurations are prepared with the following connections to the Databases:
  
  • Connection with System.Data.SqlClient: Data Source=localhost;Initial Catalog=DATABASE;Integrated Security=True;Asynchronous Processing=True;MultipleActiveResultSets=True;Application Name=\"Excel To Database\";Workstation ID=Maquina
  • Connection with MSOLEDBSQL19: Provider=MSOLEDBSQL19.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DATABASE;Application Name=\"Excel To Database\";Workstation ID=Maquina;Use Encryption for Data=Optional;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite
  • Connection with MSOLEDBSQL: Provider=MSOLEDBSQL.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DATABASE;Application Name=\"Excel To Database\";Workstation ID=Maquina;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite
  • Connection with SQLNCLI11: Provider=SQLNCLI11.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DATABASE;Application Name=\"Excel To Database\";Workstation ID=Maquina;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite
  • Connection with SQLOLEDB: Provider=SQLOLEDB.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=DATABASE;Application Name=\"Excel To Database\";Workstation ID=Maquina;mars connection=True;datatypecompatibility=80
  • Connection with ODBC Driver 18 for SQL Server: Driver={ODBC Driver 18 for SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=DATABASE;trustservercertificate=Yes;autotranslate=No;mars_connection=Yes
  • Connection with ODBC Driver 17 for SQL Server: Driver={ODBC Driver 17 for SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=DATABASE;mars_connection=Yes
  • Connection with ODBC SQL Server: Driver={SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=DATABASE;mars_connection=Yes
  • Connection with ODBC SQL Server Native Client 11.0: Driver={SQL Server Native Client 11.0};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=DATABASE;mars_connection=Yes
  
  Las conexiones ODBC utilizan los siguientes archivos DSN:
    ODBC Driver 18 SQL Server.dsn
  [ODBC]
  DRIVER=ODBC Driver 18 for SQL Server
  AutoTranslate=No
  TrustServerCertificate=Yes
  LANGUAGE=Español
  DATABASE=DATABASE
  WSID=Maquina
  APP=Excel To Database
  Trusted_Connection=Yes
  SERVER=localhost
      ODBC Driver 17 SQL Server.dsn
  [ODBC]
  DRIVER=ODBC Driver 17 for SQL Server
  TrustServerCertificate=No
  DATABASE=DATABASE
  WSID=Maquina
  APP=Sistema operativo Microsoft® Windows®
  Trusted_Connection=Yes
  SERVER=localhost
      ODBC Driver Native Client 11 SQL Server.dsn
  [ODBC]
  DRIVER=SQL Server Native Client 11.0
  DATABASE=DATABASE
  WSID=Maquina
  APP=Sistema operativo Microsoft® Windows®
  Trusted_Connection=Yes
  SERVER=localhost
      ODBC Driver SQL Server.dsn
  [ODBC]
  DRIVER=SQL Server
  UID=User
  DATABASE=DATABASE
  WSID=Maquina
  APP=Sistema operativo Microsoft® Windows®
  Trusted_Connection=Yes
  SERVER=localhost
  

• Excel Workbook

  For the tests performed, an Excel Workbook has been used, and its Sheets will be displayed as images throughout the chapter.

  It is important to note that no cells with errors have been included for the tests, so the option No Error Values will not be used.

  The data has been prepared in a way that allows testing the data load functionality. They are not an exhaustive test suite, so it can be inferred that the data load is not guaranteed to occur as the user might expect, nor as described on this page.

  You can download the test files from here: Download Examples

SQL Server Tests: "Floating Point" Numbers

To show the behavior of the drivers with this type of SQL Server data, the table whose creation script is shown below is used, along with the Excel Sheet shown in the next figure:

DROP TABLE IF EXISTS [dbo].[Tabla Numeros];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabla Numeros](
  [Real-float(24)]  [real]  NULL, --4 bytes. 7 dígitos. Precisión Simple
  [Float-float(53)] [float] NULL, --8 bytes. 15 dígitos. Precisión Doble

  [Provider]        [varchar](10) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [Driver]          [varchar](50) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [Identidad]       [int]                            IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED
(
  [Identidad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Although in SQL Server for "float" the precision can be specified, it actually only supports 2 precisions: If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.
Thus, fields of type "real" have a precision of 7 digits, and fields of type "float" have a precision of 15 digits.

SQL Server: Excel Sheet for "Floating Point" Numbers

It is important to highlight that the cell format in the Excel Sheet is "Text", "Number", or "Scientific". It is "Text" in cases where Excel’s precision is exceeded. In these cases, the point "." is used as the "Decimal Symbol", as it is the separator used by Excel To Database to convert texts into numbers.

All data providers (.NET, OLE DB, and ODBC) round the data when it exceeds the field's precision. The rounding used is of the type MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.

The maximum capabilities of Excel, SQL Server, and .NET have been tested, and the expected results were obtained with all data providers. It should be noted that both SQL Server and .NET have a precision of 7 significant digits for Single and 15 for Double.
Given the imprecision of floating-point numbers, the following changes in the data occur due to this imprecision: "B6", "B10", "A15:A16", "A19:B21". On the other hand, SQL Server performs rounding on the cells: "B4", "B6", "B7:B9", "B10", "A12:A15" and "A19:B20".
The cells "B17" and "A18" exceed the field’s capacity, and as a result, the values are replaced by the one indicated in "Default Value" in the "Mapping Panel".

SQL Server Tests: Dates

As a complement to the tests performed here, it is recommended to read the section dedicated to dates in MS Access: MS Access Tests: Dates.

Excel To Database performs the following mapping of SQL Server data types to .NET data types:

• Data Provider .NET (System.Data.SqlClient): date, smalldatetime, datetime, datetime2 -> String, time -> TimeSpan, and datetimeoffset -> DateTimeOffset.
• Data Provider OLE DB (System.Data.OleDb) and ODBC (System.Data.Odbc): date, smalldatetime, datetime, datetime2, time, and datetimeoffset -> String

ODBC (System.Data.Odbc) does not recognize the "Time" and "DateTimeOffset" data types from SQL Server. When trying to read fields from a table containing these data types, the error shown below occurs. However, in Excel To Database, great effort has been made to ensure there are no issues with these data types.

{
  "$id": "1",
  "$type": "System.ArgumentException, mscorlib",
  "Message": "Unknown SQL Type - SS_TIME_EX.",
  "ParamName": null,
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2147024809
}

Data Types:
    -155, which corresponds to DateTimeOffset type fields
    -154 (SS_TIME_EX) which corresponds to Time type fields
Are not recognized by the ODBC Data Provider for SQL Server.

Despite all efforts, with the ODBC {SQL Server} driver (which is very outdated), an error occurs when trying to insert a NULL into a field of type Time.

Row: 1
Command: INSERT INTO [dbo].[Tabla Fechas] ([Time], [Driver], [Provider]) VALUES (?, ?, ?)
Select Command: SELECT [Time], [Driver], [Provider] FROM [dbo].[Tabla Fechas]
Exception:
{
  "$id": "1",
  "$type": "System.Data.Odbc.OdbcException, System.Data",
  "Errors": {
    "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC SQL Server Driver]Característica opcional no implementada",
        "SQLState": "HYC00",
        "NativeError": 0,
        "Source": "SQLSRV32.DLL"
      }
    ]
  },
  "Source": "SQLSRV32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HYC00] [Microsoft][ODBC SQL Server Driver]Característica opcional no implementada",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: Time    Value: 
    Column: Driver    Value: {SQL Server}
    Column: Provider    Value: ODBC
An exception occurred attempting to load Table 'dbo.Tabla Fechas'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT [Time], [Driver], [Provider] FROM [dbo].[Tabla Fechas]
{
  "$id": "1",
  "$type": "System.Data.Odbc.OdbcException, System.Data",
  "Errors": {
    "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC SQL Server Driver]Característica opcional no implementada",
        "SQLState": "HYC00",
        "NativeError": 0,
        "Source": "SQLSRV32.DLL"
      }
    ]
  },
  "Source": "SQLSRV32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HYC00] [Microsoft][ODBC SQL Server Driver]Característica opcional no implementada",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

Thus, for the tests, the table and Excel Sheet shown below are used:

DROP TABLE IF EXISTS [dbo].[Tabla Fechas];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabla Fechas](
  [Date]           [date]           NULL,
  [SmallDateTime]  [smalldatetime]  NULL,
  [DateTime]       [datetime]       NULL,
  [DateTime2]      [datetime2]      NULL,
  [Time]           [time](2)        NULL, --ODBC: Tipo SQL desconocido - SS_TIME_EX.
  [DateTimeOffset] [datetimeoffset] NULL, --ODBC: Tipo SQL desconocido - -155.

  [Provider]       [varchar](10) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [Driver]         [varchar](50) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [Identidad]      [int]                            IDENTITY(1,1) NOT NULL,
PRIMARY KEY CLUSTERED 
(
  [Identidad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SQL Server: Excel Sheet for Dates

• The Influence of Cell Format

  Excel To Database uses the cell format to understand the data type contained in each cell. This means that certain data stored in Excel that contains Date and/or Time cannot be stored in specific data types of SQL Server.

  • Cells with date format:

    These data are interpreted by Excel To Database as a date only, and therefore cannot be stored in fields that only store time data: "F2", "F7", "F11", "F16", and "F21".
    For the remaining cells in column "F" (Time field of the table), the time data is extracted and stored in this field.
    To ensure proper data loading, the "No Conversion Errors" option must be checked so that these data can be replaced by the default value indicated in the "Mapping Panel".

  • Cells with time format:

    These data are interpreted by Excel To Database as time only, and therefore cannot be stored in fields that require a date: "A5:E6", "A8:E8", "A10:E10", "A14:E15", "A17:E17".
    To ensure proper data loading, the "No Conversion Errors" option must be checked so that these data can be replaced by the default value indicated in the "Mapping Panel".

  • Cells with date and time format:

    Depending on the field's data type, Excel To Database extracts the appropriate information (date only, time only, or date and time) and stores it in the field.

  Code

  Cells with date format:
  
  Cell: F2, Value: 1910/10/15, Table: dbo.Tabla Fechas, Column: Time
      Data type 'time' cannot store dates
  Cell: F2, Value: 15/10/1910, Table: dbo.Tabla Fechas, Column: Time
       Specified cast is not valid.Couldn't store <10/15/1910 11:59:59 PM> in Time Column.  Expected type is TimeSpan.
  
  Cell: F7, Value: 1910/10/15, Table: dbo.Tabla Fechas, Column: Time
      Data type 'time' cannot store dates
  Cell: F7, Value: 15/10/1910, Table: dbo.Tabla Fechas, Column: Time
       Specified cast is not valid.Couldn't store <10/15/1910 11:59:59 PM> in Time Column.  Expected type is TimeSpan.
  
  Cell: F11, Value: 1910/10/15, Table: dbo.Tabla Fechas, Column: Time
      Data type 'time' cannot store dates
  Cell: F11, Value: 15/10/1910, Table: dbo.Tabla Fechas, Column: Time
       Specified cast is not valid.Couldn't store <10/15/1910 11:59:59 PM> in Time Column.  Expected type is TimeSpan.
  
  Cell: F16, Value: 1910/10/15, Table: dbo.Tabla Fechas, Column: Time
      Data type 'time' cannot store dates
  Cell: F16, Value: 15/10/1910, Table: dbo.Tabla Fechas, Column: Time
       Specified cast is not valid.Couldn't store <10/15/1910 11:59:59 PM> in Time Column.  Expected type is TimeSpan.
  
  Cell: F21, Value: 9999-12-31 23:59:59.9999999 +00:00, Table: dbo.Tabla Fechas, Column: Time
      Data type 'time' cannot store dates
  Cell: F21, Value: 9999-12-31 23:59:59.9999999 +00:00, Table: dbo.Tabla Fechas, Column: Time
       String was not recognized as a valid TimeSpan.Couldn't store <9999-12-31 23:59:59.9999999 +00:00> in Time Column.  Expected type is TimeSpan.
  
  
  Cells with time format:
  
  Cell: A5, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type date
  
  Cell: B5, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C5, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D5, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E5, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E5, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A6, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type date
  
  Cell: B6, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C6, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D6, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E6, Value: 23:59:59.0000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.0000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E6, Value: 23:59:59.000, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59.000> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A8, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type date
  
  Cell: B8, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C8, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D8, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E8, Value: 23:59:59.4000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.4000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E8, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: Date
      Cell content could not be converted to data type date
  
  Cell: B10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: SmallDateTime
      Cell content could not be converted to data type smalldatetime
  
  Cell: C10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTime
      Cell content could not be converted to data type datetime
  
  Cell: D10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTime2
      Cell content could not be converted to data type datetime2
  
  Cell: E10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      Cell content could not be converted to data type datetimeoffset
  Cell: E10, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A14, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type date
  
  Cell: B14, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C14, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D14, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E14, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E14, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A15, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type date
  
  Cell: B15, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C15, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D15, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E15, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E15, Value: 23:59:59.000, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59.000> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  
  Cell: A17, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: Date
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type date
  
  Cell: B17, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type smalldatetime
  
  Cell: C17, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime
  
  Cell: D17, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTime2
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetime2
  
  Cell: E17, Value: 23:59:59.5000000 +02:00, Table: dbo.Tabla Fechas, Column: DateTimeOffset
      The time data 23:59:59.5000000 +02:00 cannot be stored in a field of type datetimeoffset
  Cell: E17, Value: 23:59:59, Table: dbo.Tabla Fechas, Column: DateTimeOffset
       Specified cast is not valid.Couldn't store <23:59:59> in DateTimeOffset Column.  Expected type is DateTimeOffset.
  

• Date Limits in Fields

  Depending on the field's data type, it supports different date limits. Excel To Database performs the necessary checks and notifies the user if the field limits are exceeded.

  Code

  Cell: B20, Value: 0200-10-15T23:59:59.0000000+02:00, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The date must be between 1900-01-01T00:00:00.0000000Z and 2079-06-06T23:59:29.9980000Z
  
  Cell: C20, Value: 0200-10-15T23:59:59.0000000+02:00, Table: dbo.Tabla Fechas, Column: DateTime
      The date must be between 1753-01-01T00:00:00.0000000Z and 9999-12-31T23:59:59.9970000Z
  
  Cell: B21, Value: 9999-12-31T23:59:59.9990000, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The date must be between 1900-01-01T00:00:00.0000000Z and 2079-06-06T23:59:29.9980000Z
  
  Cell: C21, Value: 9999-12-31T23:59:59.9990000, Table: dbo.Tabla Fechas, Column: DateTime
      The date must be between 1753-01-01T00:00:00.0000000Z and 9999-12-31T23:59:59.9970000Z
  
  Cell: B22, Value: 9999-12-31T23:59:59.9990000, Table: dbo.Tabla Fechas, Column: SmallDateTime
      The date must be between 1900-01-01T00:00:00.0000000Z and 2079-06-06T23:59:29.9980000Z
  
  Cell: C22, Value: 9999-12-31T23:59:59.9990000, Table: dbo.Tabla Fechas, Column: DateTime
      The date must be between 1753-01-01T00:00:00.0000000Z and 9999-12-31T23:59:59.9970000Z
  

  It is important to note that, unlike what happened with the ODBC data provider for MS Access (ODBC and Anglo-Saxon Chauvinism: 1753), this issue does not exist with SQL Server. Although, it is highlighted in the capacity of DateTime type fields.

• Time Data: Precision, Rounding, and Truncation

  Since Excel data is transmitted to the database as strings, it is the data providers and the database itself that handle the necessary truncations and rounding.
  Therefore, exhaustive tests are not performed, and it is recommended to read the detailed documentation of each data type.
  As examples, here are some of the rarest roundings:

  In datetime (Transact-SQL), it is stated that the precision for this data type is: "It rounds in increments of 0.000, 0.003, or 0.007 seconds," which matches the loaded data that contains milliseconds.

  In smalldatetime (Transact-SQL), it is stated that the precision for this data type is: "One minute." SQL Server ensures that data with seconds and/or milliseconds are rounded.

  As expected, all drivers truncate the time when data is inserted into a Date type field.

  For the OLE DB Data Provider, all drivers behave the same except for the "SmallDateTime" data type. The SQLOLEDB.1 driver rounds the data, while the rest of the drivers truncate the fractional seconds.

  The Managed Driver (System.Data.SqlClient) rounds data inserted into a "SmallDateTime" field. In other words, it behaves the same as the SQLOLEDB.1 driver.

  ODBC drivers behave differently for "Time" type fields. All drivers truncate the fractional part of the seconds for this data type. Of course, except for the ODBC {SQL Server} driver, which has issues with this data type.