SQL Server time data type is mapped to DateTime

[michal-duda]

Description

I have a problem with SQL Server time type. SQLProvider is mapping this type to DateTime but per SQL Server Data Type Mappings it should be mapped to TimeSpan. When I try to select column of time type I get System.InvalidCastException.

System.InvalidCastException
HResult=0x80004002
Message=Object must implement IConvertible.
Source=mscorlib
StackTrace:
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType)
at FSharp.Data.Sql.Common.SqlEntity.GetColumn[T](String key)
at XYZ.DataAccess.load@49.Invoke(SqlEntity trip)
at Microsoft.FSharp.Primitives.Basics.List.map[T,TResult](FSharpFunc2 mapping, FSharpList1 x)
at Microsoft.FSharp.Collections.ListModule.Map[T,TResult](FSharpFunc2 mapping, FSharpList1 list)

Repro steps

1. Create table with column of time type
2. Try to load data of this column with SQLProvider -> System.InvalidCastException

Known workarounds

I have forked repo and changed type for time to TimeSpan and everything works fine. Change is visible here.

getDbType:

        if providerType = 31
        then p.SqlDbType <- SqlDbType.DateTime
        else if providerType = 32
        then p.SqlDbType <- SqlDbType.Time

mappings:

        else if oleDbType = "time"
        then  typeof<TimeSpan>.ToString()

I can create PR for this but I don't know if this is general solution for all SQL server versions.

Related information

I am using Microsoft SQL Azure (RTM) - 12.0.2000.8

[Thorium]

I think this only applies to SQL Server 2008 and up. I think PR would be a good idea.

There are also some other issues with the older versions of SQL Servers so I think we should have some switch for backward-compatibility (if we don't already have), e.g. try to query the version once at the startup.

[michal-duda]

I think there was no time data type before SQL server 2008 (data types for 2005 and for 2008).

I have opened PR.

[Thorium]

@michal-duda any ideas how we could get this working on SQL Express on Linux which don't have the time-mapping?

[michal-duda]

@Thorium probably check for SQL server version? Hard to tell without trying... Is it reproducible only with Linux version? I don't have Linux anywhere but I can try with linux docker package. Will take me some time though.

[Thorium]

I think this is more of SQL server than OS issue.