Bug 39116 - SQL Server: datetimeoffset columns read as nvarchars
Summary: SQL Server: datetimeoffset columns read as nvarchars
Status: NEW
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: 2016-08-18 01:47 UTC (History)
3 users (show)

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


Attachments

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.

Note You need to log in before you can comment on or make changes to this bug.