Bug 39116 - SQL Server: datetimeoffset columns read as nvarchars
Summary: SQL Server: datetimeoffset columns read as nvarchars
Status: RESOLVED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: System.Data (show other bugs)
Version: 4.2.0 (C6)
Hardware: PC Linux
: --- normal
Target Milestone: Untriaged
Assignee: Bugzilla
URL:
Depends on:
Blocks:
 
Reported: 2016-02-25 12:21 UTC by pzavolinsky
Modified: 2018-02-22 22:25 UTC (History)
4 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description pzavolinsky 2016-02-25 12:21:51 UTC
When reading a column of type "datetimeoffset" from a SQL Server database, the actual type of the value read is 'nvarchar' (System.String) instead of 'datetimeoffset' (System.DateTimeOffset).

Repro code follows:

1) In the DB execute:
CREATE TABLE [datetest] (a_date datetimeoffset);
INSERT INTO [datetest] VALUES ('2017-01-01 00:00:00.0000000 +00:00');

2) Then replace <YOUR_CONNECTION_STRING> and run:
using System;
using System.Data.SqlClient;

namespace DateTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("<YOUR_CONNECTION_STRING>"))
            {
                conn.Open();
                using (var cmd = new SqlCommand("SELECT TOP 1 a_date FROM [datetest]", conn))
                using (var reader = cmd.ExecuteReader())
                {
                    Console.WriteLine("Expected 'datetimeoffset', got '{0}'", reader.GetDataTypeName(0));
                }
            }
        }
    }
}

Output in .NET (4.6.1):
Expected 'datetimeoffset', got 'datetimeoffset'

Output in Mono (4.2.2):
Expected 'datetimeoffset', got 'nvarchar'


Environment
-----------
$ mono --version
Mono JIT compiler version 4.2.2 (Stable 4.2.2.30/996df3c Mon Feb 15 17:30:30 UTC 2016)
Copyright (C) 2002-2014 Novell, Inc, Xamarin Inc and Contributors. www.mono-project.com
        TLS:           __thread
        SIGSEGV:       altstack
        Notifications: epoll
        Architecture:  amd64
        Disabled:      none
        Misc:          softdebug
        LLVM:          supported, not enabled.
        GC:            sgen

$ lsb_release  -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.4 LTS
Release:        14.04
Codename:       trusty

$ cat /proc/cpuinfo | grep 'model name' | uniq
model name      : Intel(R) Core(TM) i7-4900MQ CPU @ 2.80GHz

$ dpkg -l | grep 'mono-runtime'
ii  mono-runtime                                                4.2.2.30-0xamarin2                   amd64        Mono runtime - default version
ii  mono-runtime-common                                         4.2.2.30-0xamarin2                   amd64        Mono runtime - common files
ii  mono-runtime-sgen                                           4.2.2.30-0xamarin2                   amd64        Mono runtime - SGen

Database
--------
SELECT @@VERSION
Microsoft SQL Server 2014 - 12.0.2269.0 (X64) 
	Jun 10 2015 03:35:45 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Analysis
--------
From https://github.com/mono/mono/blob/master/mcs/class/System.Data/System.Data.SqlClient/SqlDataReader.cs:

The switch in GetSchemaRowType lacks a case for DateTimeOffset:

private void GetSchemaRowType (TdsColumnType ctype, int csize,
                                       short precision, short scale,
                                       out int dbType, out Type fieldType,
                                       out bool isLong, out string typeName)
{
    ...
    switch (ctype) {
        ...
        // missing case for TdsColumnType.DateTimeOffset
        ...
    }
    ...
}

Probably should be something like this:

private void GetSchemaRowType (TdsColumnType ctype, int csize,
                                       short precision, short scale,
                                       out int dbType, out Type fieldType,
                                       out bool isLong, out string typeName)
{
    ...
    switch (ctype) {
        ...
        case TdsColumnType.DateTimeOffset  :
            typeName = "datetimeoffset";
            dbType = (int) SqlDbType.DateTimeOffset;
            fieldType = typeof (DateTimeOffset);
            isLong = false;
            break;
        ...
    }
    ...
}

Not sure if this would be enough to have full support of datetimeoffsets but surely looks like a step in the right direction.
Comment 1 Christoffer G. Thomsen 2016-02-25 13:54:14 UTC
I am experiencing the same issue, but with the SQL Server 'date' type being interpreted as 'nvarchar' instead of datetimeoffset. Looks like there isn't a switch for this type either in SqlDataReader.cs.
Comment 2 Andrés Botero 2016-08-18 01:47:54 UTC
I am experiencing the same issue with the same datatype, i'm getting an exception of assigning an System.String to DatetimeOffset because Mono is returning an nvarchar.
Comment 3 Marek Safar 2018-02-22 22:25:37 UTC
Mono 5.10 has significantly improved System.Data implementation which should resolve this issue. If you can still reproduce it please reopen the issue.