Introduction

UTC handling with Dapper is less about Dapper itself and more about database type semantics plus application conventions. Many bugs come from reading DateTime values with unspecified kind and then converting incorrectly on the assumption that the clock value itself is wrong. The safest approach is to standardize storage and mapping rules across write and read paths so every timestamp has a clear meaning.

Understand Database Type Behavior

Different SQL types preserve different timezone information.

Common SQL Server choices:

• datetime and datetime2 store date and time but no timezone offset.
• datetimeoffset stores date, time, and offset.

When you read datetime or datetime2 into C#, DateTime.Kind is often Unspecified. If your team assumes stored values are UTC, you must mark them as UTC after reading or prefer DateTimeOffset mapping.

Write UTC Values Consistently

Always generate timestamps using UTC at write time.

1const string insertSql = @"
2INSERT INTO Orders (CreatedAtUtc)
3VALUES (@CreatedAtUtc);";
4
5await connection.ExecuteAsync(insertSql, new
6{
7    CreatedAtUtc = DateTime.UtcNow
8});

Do not mix local time writes with UTC reads. Mixed semantics are the main cause of drift.

Read as DateTime and Normalize Kind

If the column is UTC but stored in datetime2, explicitly set kind after retrieval.

1const string query = "SELECT CreatedAtUtc FROM Orders WHERE Id = @Id";
2var value = await connection.QuerySingleAsync<DateTime>(query, new { Id = orderId });
3
4var utc = DateTime.SpecifyKind(value, DateTimeKind.Utc);
5Console.WriteLine(utc.ToString("O"));

This does not change clock value, only kind metadata, which is what you want for UTC stored values.

That distinction matters. SpecifyKind is correct only when the stored value already represents UTC and merely lacks metadata. If the database actually contains local time, SpecifyKind would silently label the wrong moment as UTC.

Prefer DateTimeOffset for Fewer Ambiguities

DateTimeOffset preserves offset context and often avoids accidental kind mistakes.

1const string query = "SELECT CreatedAtOffset FROM Orders WHERE Id = @Id";
2var dto = await connection.QuerySingleAsync<DateTimeOffset>(query, new { Id = orderId });
3
4var utc = dto.UtcDateTime;
5Console.WriteLine(utc.ToString("O"));

If you control schema design, datetimeoffset plus DateTimeOffset mapping is usually easier to reason about over time.

Add a Dapper Type Handler for Centralized Policy

For larger codebases, a type handler can enforce UTC normalization consistently.

1using System;
2using System.Data;
3using Dapper;
4
5public sealed class UtcDateTimeHandler : SqlMapper.TypeHandler<DateTime>
6{
7    public override DateTime Parse(object value)
8    {
9        var dt = (DateTime)value;
10        return DateTime.SpecifyKind(dt, DateTimeKind.Utc);
11    }
12
13    public override void SetValue(IDbDataParameter parameter, DateTime value)
14    {
15        parameter.Value = value.Kind == DateTimeKind.Utc
16            ? value
17            : value.ToUniversalTime();
18    }
19}

Register at startup:

SqlMapper.AddTypeHandler(new UtcDateTimeHandler());

Centralizing the rule reduces per query mistakes.

Test Round Trip Behavior

Write tests that insert known UTC values, read them back, and compare using round trip format.

1var nowUtc = DateTime.UtcNow;
2// insert and read...
3Assert.Equal(DateTimeKind.Utc, read.Kind);
4Assert.True((read - nowUtc).Duration() < TimeSpan.FromSeconds(1));

Also test serialization boundaries if values are sent through JSON APIs, because timezone handling can shift there too.

API Serialization Boundaries

Even with perfect Dapper mapping, UTC bugs can appear when serializing to JSON and deserializing on clients. Standardize output format using ISO round trip strings and verify clients treat them as UTC.

1var payload = new
2{
3    CreatedAt = utc.ToString("O")
4};

If APIs expose both local and UTC forms, name fields explicitly such as createdAtUtc and createdAtLocal to prevent consumer confusion.

Clear naming helps at the database level too. A column named CreatedAtUtc communicates intent better than CreatedAt, and that alone prevents many incorrect conversions during maintenance work.

Common Pitfalls

• Storing local times while naming columns as UTC.
• Assuming DateTime.Kind is UTC when reading datetime2 values.
• Mixing DateTime and DateTimeOffset conventions without clear policy.
• Converting unspecified values with wrong assumptions about source timezone.
• Handling UTC conversion ad hoc in many repositories instead of centralized logic.

Summary

• Decide and document a strict UTC policy for storage and retrieval.
• Use UTC at write time and normalize kind semantics at read time.
• Prefer DateTimeOffset when schema and API design allow it.
• Consider Dapper type handlers to enforce rules centrally.
• Validate with round trip tests to catch timezone drift early.