Hi

LLBLGEN v2.6 - What is the best way to use the Predicate system to compare DATETIMES using just times - that is ignore the date part.

I've tried

filter.AddWithAnd(ActivityFields.StartTime >= EntryTime)

But I don't get the correct results.

Thanks Mark

In SQL you can Use DATEPART() function.

Similarly you can use it, by utilising DbFunctionCall

Hi David

Thanks for the help so far. I have no problem with the SQL part - I can use cast to remove the date component.

SELECT [ActivityID]
      ,[Session]
      ,[StartTime]
      ,CAST(StartTime as time)[Time]
      ,[Duration]
  FROM [Merlin].[dbo].[Activity]

But having read the DbFunctionCall examples the syntax has me confused. I can't why I need the second DbFunctionCall ?

// create a filter which filters on month equals 3
IPredicate monthFilter = new EntityField("OrderMonth", new DbFunctionCall("CAST({0} AS bigint)",   new object[] { new DbFunctionCall("MONTH", new object[] { OrderFields.OrderDate }) })) == 3;

I would have thought something like this would work for a range check where DBTime >= STARTTIME <= DBTime+Duration

EntityField time = new EntityField("ActivityTime", new DbFunctionCall("CAST({0} AS time)",new object[] {ActivityFields.StartTime}));
filter.AddWithAnd( new PredicateExpression(time >= StartTime));
filter.AddWithAnd(new PredicateExpression(time < StartTime.Value.AddMinutes(Duration)));

Or I am making a meal of this?

Mark

Hi Mark,

From your examples I'm not sure what you would want to achieve (filter by the month, or just filter the time). Anyway, you can use your nested DBFunctionCall, that's totally fine, or you can use the second one as a constant, like:

IPredicate monthFilter = new EntityField("OrderMonth", 
     new DbFunctionCall("CAST(MONTH({0}) AS bigint)", new object[] { OrderFields.OrderDate })) == 3;

Hi David,

I'm not interested in months. The first code example is just taken from the LLBLGEN documentation you referenced in your first answer - it didn't make any sense to me, too many DBFunctionCalls. Your last example does.

I noticed 2 problems with my suggested code. First LLBLGEN doesn't seem to handle the Timespan type and converts it to a string in the SQL which then fails (possibly an IConvertible issue)

I also noticed the generated SQL has two casts producing the same data, which seems odd.

How do you suggest I get around the SQL TIME <-> C# Timespan, if LLBLGEN doesn't convert Timespans?

Thanks Mark

Hi David

I have managed to get the result I need using the following code - which CASTs to an SQL Time and then back to DateTime - resetting the date portion to 1900-01-01

DateTime start = DateTime.Parse("1900-01-01").Add(StartTime.Value.TimeOfDay);

EntityField time = new EntityField("ActivityTime", new DbFunctionCall("CAST({0} AS datetime)",
   new object[] { new DbFunctionCall("CAST({0} AS time)", new object[] { ActivityFields.StartTime})}));

filter.Add(new FieldBetweenPredicate(time, startTime, startTime.AddMinutes(duration)));

The SQL generated is spot on!

If there's a better way (possibly with Timespans) let me know.

Thanks Mark