Hello,

Been studying the proposed workaround to access DATEPART functionality from SQL2005 within LLBLGEN:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I am having problems with the predicateexpression. The actual field in the DB is date time, but since I have converted the datetime to day of the month (int, 1 - 31 days), I would like to provide an int in the predicate. I am unable to do so:

fields = new ResultsetFields(1);
IExpression datepartCall = new FunctionExpression(RawLogsFields.Date), "DATEPART");
fields.DefineField(RawLogsFields.Date, 0, "dateDay");
fields[0].SetExpression(datepartCall);
            
sorter = new SortExpression(new SortClause(fields["dateDay"], null,  SortOperator.Ascending, "dateDay"));

groupByClause.Add(fields["dateDay"]);

//here is the problem, how do I compare the "dateDay" field to an int.  The date field is DateTme in the database.  How do I say "all days greater than day 7 of the month" ?           
bucket.PredicateExpression.Add(RawLogsWmsFields.Date >= 7);

adapter.FetchTypedList(fields, results, bucket, 0, sorter, false, groupByClause);

What's the llblgen pro version you're using?

The Designer runtime version is 2.0.0.0 FINAL (Feb 14, 2007). Using Adapter model.

Thanks!

That's great, in V2.0 you can use **DBFunctionCalls **(_Ref: LLBLGenProHelp - Using generated code - Calling a database function_).

The following example retrieves the _totalFreight _per year or the _Orders _table from records where year > 1995.

// define fields
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(OrdersFields.OrderDate, 0, "Year");
fields.DefineField(OrdersFields.Freight, 1, "SumOfFreight",  AggregateFunction.Sum);
            
// expression to apply for DBFunctionCall
fields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { OrdersFields.OrderDate });

// groupBy
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

// filter on dbfuntionCall field
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(  
    new FieldCompareValuePredicate(fields[0], null, ComparisonOperator.GreaterThan, 1995));
    
// retrieve
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter, 0, null, false, groupBy);
}

So aply the concept to your DAY case and let us know if everything is ok.