`DateTime` loses precision when bound as a parameter
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
DateTime ticks are truncated when they are used as a parameter. (e.g., '2020-01-01 00:00:00.1333333' is bound as '2020-01-01 00:00:00.1330000')
Here is a repro illustrating the issue. (note that a vanilla SqlCommand works!)
Please let me know if this is an issue on my end, or if it's with the repo, where the fix might be. I'm happy to open a PR.
using Dapper;
using Microsoft.Data.SqlClient;
using System;
namespace DapperDateTimeRepro
{
class Program
{
static void Main(string[] args)
{
var connectionString = new SqlConnectionStringBuilder
{
DataSource = "(localdb)\\MSSQLLocalDB",
IntegratedSecurity = true,
Pooling = true
}.ConnectionString;
var dateTimeWithTicks = new DateTime(2020, 1, 1, 0, 0, 0, DateTimeKind.Utc);
dateTimeWithTicks = dateTimeWithTicks.AddTicks(1333333);
using (var conn = new SqlConnection(connectionString))
{
// 2020-01-01 00:00:00:.1330000
var roundTrip = conn.QuerySingle<DateTime>("SELECT @x", new { x = dateTimeWithTicks });
var roundTripEquals = roundTrip == dateTimeWithTicks; // false!
var readOnly = conn.QuerySingle<DateTime>(@"
DECLARE @existing datetime2 = '2020-01-01 00:00:00.1333333'
SELECT @existing");
var readOnlyEquals = readOnly == dateTimeWithTicks; // true!
// now use SQLCommand instead of Dapper
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "SELECT @x";
var param = command.CreateParameter();
param.DbType = System.Data.DbType.DateTime2;
param.Value = dateTimeWithTicks;
param.ParameterName = "x";
command.Parameters.Add(param);
var reader = command.ExecuteReader();
reader.Read();
var noDapper = (DateTime)reader.GetValue(0);
noDapper = DateTime.SpecifyKind(noDapper, DateTimeKind.Utc);
reader.Close();
var noDapperEquals = noDapper == dateTimeWithTicks; // true!
}
}
}
}
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the supplied C# repro, comparing Dapper's parameter binding with the Microsoft.Data.SqlClient SqlCommand path. Trace how the DateTime parameter is created and verify the fix by confirming that the round-trip value retains the original ticks, matching the direct SqlCommand result.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100