Repodb: Question: Question on how to convert query field and value to DbTypes for comparison

[fenilrathod]

I am finding a way to convert/cast Query Field and it's value to DBType. This is needed while comparing Dates ignoring time. I guess it can be achieved if we cast field to date and compare afterwards.

There is a SqlServerConvertFieldResolver and it can be used for this. But it needs to be used along with custom statement builder. Is there any other simple way to just include casting requirements into QueryField directly?

[mikependon]

Do you really need to cast or just a type mapping? There is no way you can cast a QueryField, but it should be covered by type mapping if you do so. Have you tried the TypeMap atrribute decoration? Or the TypeMapper.Add or FluentMapper.Type<TType>() method.

[fenilrathod]

Basically I need all rows of given date. Database column gas date with
time. And I believe CAST as DATE needed to ignore time part of database
column having type DateTime.

Other workaround I just thought is to use Between operator and pass
QueryField value having dates along with time. Like 2020-07-28 00:00 to
2020-07-28 23:59. I will give it a try.

On Tue 28 Jul, 2020, 12:19 AM Michael Camara Pendon, <
[email protected]> wrote:

Do you really need to cast or just a type mapping? There is no way you can
cast a QueryField, but it should be covered by type mapping if you do so.
Have you tried the TypeMap atrribute decoration? Or the TypeMapper.Add or
FluentMapper.Type() method.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/mikependon/RepoDb/issues/491#issuecomment-664574171,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AG43B7O5MV6UGSDXQOZZE5TR5XD2RANCNFSM4PJCWRUQ
.

[mikependon]

Great. Now I fully understand your case. I think it's pretty normal to just use the expression-based query on your case, and/or if you're adventurous enough, diving into QueryField as you use now is much better.

Via Expression:

var from = DateTime.UtcNow.AddMonths(-1).Date;
var to = DateTime.UtcNow;
using (var connection = new SqlConnection(ConnectionString))
{
     connection.Query<EntityModel>(e => e.DateTimeColumn >= from && e.DateTimeColumn <= to);
}

Or Via QueryField:

var from = DateTime.UtcNow.AddMonths(-1).Date;
var to = DateTime.UtcNow;
using (var connection = new SqlConnection(ConnectionString))
{
     connection.Query<EntityModel>(new QueryField("DateTimeColumn", Operation.Between, new[] { from, to }));
}

That kind of query would give you the correct data from exactly a month from now (UTC).

[fenilrathod]

Right. I've used QueryField with Between operator and it is working fine. I was into more SQL scripting zone previously :)
Thanks for your time!

[mikependon]

No worries and thanks for confirming. Closing this one for now.