Using Dapper-dot-net, how do I map SQL Time column to a .Net type?

Question

I have an existing database that uses the SQL time(7) type, which does not map directly to a .Net type.

Question: Using Dapper, how do I map a SQL time column to a .Net type?

Issue: When I attempt to implicitly map a SQL time(7) column to DateTime using Dapper-dot-net, I get the following exception:

An exception of type 'System.Data.DataException' occurred in Dapper.dll but was not handled in user code

Additional information: Error parsing column 5 (CheckInTime=08:54:43.1470000 - Object)

I assumed it would map to DateTime automatically, but it does not appear to do so

Answer 1

I've had some success mapping SQL time(7) columns to System.TimeSpan.

As Dan pointed out, a TimeSpan is "semantically" less appropriate if your intent is to represent a time of the day.

But I think that, technically, there is no loss of information going from time(7) to TimeSpan, so the data is there, and it's a matter of meaning.

One way I've been using to alleviate the ugliness is to use a private TimeSpan property just for the sake of querying and expose the data in a custom type public property.

I prefer that over using a DateTime in combination with the call to CONVERT() in the SQL, because even with the DateTime you still have a somehow "imperfect" representation of you data (the date part is bogus) and you introduce ugliness in the SQL as well.

But, at this point, I think it can be considered a matter of personal preference.

Answer 2

It appears that you must explicitly cast/convert the time column in T-SQL to either DateTime or TimeSpan depending on your need.

If your time represents a time of day (i.e. 4:15pm)

string sql = @"SELECT CONVERT(DATETIME, InTime) AS CheckInTime FROM TimeLog";

var checkInTimes = _conn.Query<DateTime>(sql).ToList();

If your time represents a nullable time of day (i.e. 4:15pm or null)

To handle a nullable time, you just change the target .Net type to nullable (DateTime?):

// Note: the OutTime column is nullable
string sql = @"SELECT CONVERT(DATETIME, OutTime) AS CheckOutTime FROM TimeLog";

var checkOutTimes = _conn.Query<DateTime?>(sql).ToList();

Note that the date part of the resulting DateTime object will be set to 1900-01-01.

If your time represents an amount of time (i.e. 2 hours and 47 minutes running time)

In this case it is better to convert to TimeSpan which will not include the invalid 1900 date, but also does not allow for AM/PM formatting in .ToString()

string sql = @"SELECT RunningTime FROM TimeLog";
var movieLength = _conn.Query<TimeSpan>(sql).ToList();

Thanks to Mauro for pointing out this use case.