SqlBulkCopy - DateTime wrong conversion

Question

I'm getting query results from Azure Log Analytics. The results are in Table object. It's all in string format. In the database, table has column PeriodStart, which is smalldatetime type.

The code below works fine - it inserts all the data. But it converts PeriodStart wrong. For example, I have string value in row for PeriodStart column "2019-03-26T00:00:00Z" - and in database it will look "2019-03-26 02:00:00". Seems like it uses my timezone - converts to local time. How to avoid this?

public async Task BulkInsertMetrics(Table metrics)
{
 var metricsDt = new DataTable();
 metricsDt.Columns.AddRange(metrics.Columns
 .Select(c => new DataColumn(c.Name)).ToArray());
 foreach (var row in metrics.Rows)
 {
 metricsDt.Rows.Add(row.ToArray());
 }
 using (var connection = new SqlConnection(_databaseSettings.ConnectionString))
 {
 await connection.OpenAsync();
 using (var transaction = connection.BeginTransaction())
 using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
 {
 bulkCopy.DestinationTableName = "Metrics";
 foreach (var column in metrics.Columns)
 {
 bulkCopy.ColumnMappings.Add(column.Name, column.Name);
 }
 await bulkCopy.WriteToServerAsync(metricsDt);
 transaction.Commit();
 }
 }
}

• Dan Guzman: Use a strongly-typed datetime column the source DataTable to avoid parsing and string formatting issues.
• qwermike: Okay, manual converting helped. I used ToUniversalTime() after parsing string. Thanks.
• Dan Guzman: I expanded on your solution with an answer.

Answer 1

When you pass a date time string, it must be parsed by the client API and converted to the target smalldatetime data type, which has no notion of time zone. .NET recognizes the ISO 8601 datetime string with the "Z" denoting UTC and converts it to your local time by default. To wit:

DateTime.Parse("2019-03-26T00:00:00Z") //converted to your local time
DateTime.Parse("2019-03-26T00:00:00") //no conversion

One workaround is to use the ToUniversalTime() method to avoid the conversion to your local time zone:

DateTime.Parse("2019-03-26T00:00:00Z").ToUniversalTime()

It is generally a best practice to use a DataTable with native data types containing the desired value so that you have full control over the actual value inserted into the database.