How can I get Dapper to map .net datetime to datetime2?

Question

Pretty simple, I'm converting our existing system from EF to Dapper. For various corporate reasons we can't really change the database, some of the tables have columns that are of type DateTime2. Dapper converts any .net DateTime to DbType.DateTime.

Someone must have bumped against this before and found an easy solution ?

John Tseng · Answer 1 · 2017-12-11T19:18:14.053

59

There's a much easier solution now in a similar question:

SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTime2);

This must be applied before INSERT's. Thanks, @Igand.

edited Dec 11 '17 at 19:18

answered Dec 01 '15 at 21:50

John Tseng

6,262
2
27
35

3

Note to future generations: If this line doesn't seem to help, make sure the mapping was applied _before_ you INSERT the data and still holds before you try to SELECT them. Its obvious, but took me over hour to figure, why test keeps failing :) – Igand Dec 11 '17 at 09:51
Thanks, @Igand Just added it in the answer so no one misses it. =) – John Tseng Dec 11 '17 at 19:18

Randolpho · Accepted Answer · 2012-06-27T20:40:36.527

9

Dapper is litterally a single file that you include into your code base. Just edit the file:

Replace (around line 300):

        typeMap[typeof(Guid)] = DbType.Guid;
        typeMap[typeof(DateTime)] = DbType.DateTime;
        typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
        typeMap[typeof(byte[])] = DbType.Binary;

With:

        typeMap[typeof(Guid)] = DbType.Guid;
        typeMap[typeof(DateTime)] = DbType.DateTime2;
        typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
        typeMap[typeof(byte[])] = DbType.Binary;

Edit:
There's also a nullable DateTime further down that block of mappings, around line 319:

        typeMap[typeof(DateTime?)] = DbType.DateTime;
        typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;

To:

        typeMap[typeof(DateTime?)] = DbType.DateTime2;
        typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;

edited Jun 27 '12 at 20:40

answered Jun 27 '12 at 17:22

Randolpho

55,384
17
145
179

5

What if some are DateTime and others are DateTime2? – Metro Smurf Jun 27 '12 at 17:31
@MetroSmurf Actually, I think sticking with DateTime2 is the best choice. DATETIME2 has an implicit conversion to DATETIME and vice versa in SQL Server. The only problem will be loss of precision on the time stamp, because the .Net DateTime is more precise than the SQL Server DATETIME. This has been a known issue for years, however, which is why it's recommended to use DATETIME2 in the first place. – Randolpho Jun 27 '12 at 17:46
1

I did try this. However, you cannot insert a DateTime2 into a DateTime in SQL. You get an error. Or to be more accurate, I guess that if you declare your command parameter as DateTime2, SQL will not allow you to insert it into DateTime (I'm not sure if SQLClient is blocking it or if it is SQL itself). – Dirk Jun 27 '12 at 19:04
Oh, I just realised, when I tested this, the date I was testing with still had MinValue, which is why SQL did not allow it to go into DateTime. So I guess I was on the right track all along. – Dirk Jun 27 '12 at 19:32
Heh, I just noticed your followup reply. If it works, it works; I'll leave my edit in place in case somebody else needs it. – Randolpho Jun 27 '12 at 19:40
" When dates are sent to SQL Server, they're sent as a string, " - pretty sure they would be going as binary dates – Marc Gravell Jun 27 '12 at 19:42
@MarcGravell you're right. I forgot that .Net `SqlCommand` objects will wrap the `CommandText` for a `Text` type command in an *RPC-style call* to `sp_executesql`, which will cause the parameters to be sent in TDS native formats rather than as raw text. I blame the fact that I've been doing text-only queries on node.js recently. – Randolpho Jun 27 '12 at 20:39
7

Editing libraries is an extremely poor idea. It makes upgrading much more difficult. Also, this is no longer true. It's now a binary. – jpmc26 Jan 10 '19 at 05:11

score 1 · Answer 3 · answered Jun 11 '19 at 23:59

For Date and time data with time zone awareness.

SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.DateTimeOffset);

I do not know why when I tried to use Datetime2, I still kept losing the milliseconds. This DateTimeOffset type is also Datetime2.

The date value range is from January 1,1 AD through December 31, 9999 AD. The time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range is -14:00 through +14:00.

score 1 · Answer 4 · answered Sep 06 '21 at 14:30

The solutions provided will map the types globally because the list of mappings is static.

To create a list of parameters for a single command a better approach is to use the DynamicParameters class.

var parameters = new DynamicParameters(template);
parameters.Add("@DateTimeParam", dateTimeValue, DbType.DateTime2);
    
await connection.ExecuteAsync(sql, parameters);

Where template can be any object (including the previous parameters used). The template properties are going to be merged with the added parameters.

How can I get Dapper to map .net datetime to datetime2?

4 Answers4

Linked