Mapping for DateTime2

[andreister]

I use datetime2 in SqlServer to store datetimes with increased accuracy, because the legacy datetime gets rounded on nanoseconds (note also that Microsoft themselves recommend the replacement)

And to get this working in Dapper I had to update its mappings with

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

Legacy datetime columns continue to work with the above, because SQL Server has an implicit convertsion from datetime2 to datetime so all seems fine.

Would you like to get this into Dapper, or do you think there's a better way? I can make a pull request with a unit test, if you wish

[princeoffoods]

+1. We're applying the same changes too.

[mgravell]

The difficulty I have with this is: what is the behavior with the wide range of ADO.NET providers that aren't SQL Server; for example, does it work on Oracle? It is hard to reliably detect the provider, for a range of reasons. This is almost the sort of thing I'd want to do only if it is is SqlCommand, but then that still wouldn't work with a few tools that work as ADO.NET decorators

On 5 January 2015 at 09:46, Andrew Kazyrevich [email protected] wrote:

I use datetime2 in SqlServer to store datetimes with increased accuracy, because the legacy datetime gets rounded on nanoseconds (note also that Microsoft themselves recommend http://technet.microsoft.com/en-us/library/ms187819.aspx the replacement)

And to get this working in Dapper I had to update its mappings https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper%20NET40/SqlMapper.cs#L703 with

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

Legacy datetime columns continue to work with the above, because SQL Server has an implicit convertsion from datetime2 to datetime so all seems fine.

Would you like to get this into Dapper, or do you think there's a better way? I can make a pull request with a unit test, if you wish

— Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/229.

Regards,

Marc

[dimzon]

I propose to collect different behaviors into strategy pattern and allow to

1. by default - auto-detect behavior by connection class name
2. override 1) via SqlMapper.SetStrategy(SqlMapper.WellKnownStrategy.Oracle)
3. pass concrete behavior as optional parameter in concrete method

also (for parameters) I propose special class TypedParamValue giving 100% control over ADO.NET parameter creation: cn.Query<X>("select @dt, @str", new {dt=new TypedParamValue{Value=DateTime.UtcNow,DbType=DbType.DateTime2}, str=new TypedParamValue{Value="foo",DbType=DbType.AnsiiString}})

[acalbazana]

Question about this... If I am using the binary for Dapper and NET45, how can I change the SqlTypeMap to support DateTime2? I see that NET40 has a static SqlMapper with a method available to add to the map to accommodate DateTime2. But NET45 does not have this method available to it.

Thanks,

Alejandro

[IanKemp]

Any progress on this?

[FenrisWolfAtMiddleEarth]

Could you not just define a DateTime2 class ? And then add that to the SqlMapper ?

SqlMapper.AddTypeMap(typeof(DateTime2), DbType.DateTime2);

a DateTime2 like this should work.

` public class DateTime2 : IConvertible { private readonly DateTime _value;

    public DateTime2(DateTime value)
    {
        _value = value;
    }

    public static implicit operator DateTime(DateTime2 value)
    {
        return value._value;
    }

    public static implicit operator DateTime2(DateTime value)
    {
        return new DateTime2(value);
    }

    public TypeCode GetTypeCode()
    {
        throw new NotImplementedException();
    }

    public bool ToBoolean(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public byte ToByte(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public char ToChar(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public DateTime ToDateTime(IFormatProvider provider)
    {
        return _value;
    }

    public decimal ToDecimal(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public double ToDouble(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public short ToInt16(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public int ToInt32(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public long ToInt64(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public sbyte ToSByte(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public float ToSingle(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public string ToString(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public object ToType(Type conversionType, IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public ushort ToUInt16(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public uint ToUInt32(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }

    public ulong ToUInt64(IFormatProvider provider)
    {
        throw new NotImplementedException();
    }
}

`

Defining your value-object with DateTime2 types instead of just DateTime will parse them to SQLServer as DbType.DateTime2

[ravindUwU]

@NickCraver thoughts on adding a DbDateTime class that would allow the caller to specify the DbType of a date parameter without changing any globals, similar to DbString?

class DbDateTime : SqlMapper.ICustomQueryParameter
{
	public DateTime Value { get; set; }

	public bool IsDateTime2 { get; set; }

	public void AddParameter(IDbCommand command, string name)
	{
		IDbDataParameter param;
		if (command.Parameters.Contains(name))
		{
			param = (IDbDataParameter)command.Parameters[name];
		}
		else
		{
			param = command.CreateParameter();
			command.Parameters.Add(param);

			param.ParameterName = name;
		}

		param.Value = Value;
		param.DbType = IsDateTime2 ? DbType.DateTime2 : DbType.DateTime;
	}

	public override string ToString() =>
		$"DbDateTime (Value: '{Value}', IsDateTime2: {IsDateTime2})";
}

async Task Query<T>(T date)
{
	var res = await conn.QueryAsync(
		sql: $"""
			SELECT q.*
			FROM (VALUES
				(1, CAST('2023-01-01 00:00:00.123' AS DATETIME2)),
				(2, CAST('2023-01-01 00:00:00.124' AS DATETIME2)),
				(3, CAST('2023-01-01 00:00:00.125' AS DATETIME2)),
				(4, CAST('2023-01-01 00:00:00.126' AS DATETIME2))
			) AS q(n, d)
			WHERE q.d >= @date
			""",
		param: new
		{
			date,
		}
	);

	Console.WriteLine($"{res.Count()} rows using {date.GetType().Name} parameter {date}:");
	foreach (var row in res)
	{
		Console.WriteLine($"\t{row}");
	}
}

var date = new DateTime(2023, 1, 1, 0, 0, 0, 125);

// These set DbType.DateTime parameters, and don't return any rows.
// SELECT CAST('2023-01-01 00:00:00.125' AS DATETIME) returns '2023-01-01 00:00:00.127', so none of
// the rows satisfy the predicate WHERE q.d >= @date.
await Query(date);
await Query(new DbDateTime { Value = date });

// This sets a DbType.DateTime2 parameter, and returns rows 3 & 4.
await Query(new DbDateTime { Value = date, IsDateTime2 = true });

0 rows using DateTime parameter 1/01/2023 12:00:00 AM:

0 rows using DbDateTime parameter DbDateTime (Value: '1/01/2023 12:00:00 AM', IsDateTime2: False):

2 rows using DbDateTime parameter DbDateTime (Value: '1/01/2023 12:00:00 AM', IsDateTime2: True):
        {DapperRow, n = '3', d = '1/01/2023 12:00:00 AM'}
        {DapperRow, n = '4', d = '1/01/2023 12:00:00 AM'}