Hi,

I'm using LLBLGen 2 with Sql Server 2005 in an adapter configuration.

I need to retrieve rows from a table based on the day part of a smalldatetime variable, in other words ignoring the Year, Month and Time values. How would I set up the predicate for such a query.

The filter I'm using:

public EntityCollection GetMonthlyPoliciesByInstallmentDate (int productId, int day)
{
    EntityCollection coll = new EntityCollection(new PolicyEntityFactory());
    using (DataAccessAdapter adapter = new DataAccessAdapter ())
    {
        // PREFETCH PATH
        IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.PolicyEntity);

        // FILTER
        IRelationPredicateBucket filter = new RelationPredicateBucket();
        // FirstInstallmentPayableDate is a smalldatetime variable. I need to compare only the day part of the date.
        filter.PredicateExpression.Add(PolicyFields.FirstInstallmentPayableDate == day);

        // SORTER
        ISortExpression sorter = new SortExpression();
        sorter.Add(PolicyFields.PolicyId | SortOperator.Ascending);

        // FETCH
        adapter.FetchEntityCollection(coll, filter, 0, sorter, prefetchPath);
        log.Info("Fetched monthly Credit Life Policies.");
        return coll;
    }
}

• option 1: create a day column on your table which is an expression of the date column.

CREATE TABLE [my_table] (
    [order_date] [datetime] NOT NULL ,
    [order_day] AS (datepart(day,[order_date])) 
)

and filter on this

IPredicateExpression filter = new PredicateExpression(MyTableFields.OrderDay == myVariable);

• option 2: use the DbFunctionCall object

IEntityField2 dayFilter = new EntityField2("filterByDay");
dayFilter.ExpressionToApply = new DbFunctionCall( "DAY", new object[] { MyTableFields.OrderDate } );
IPredicateExpression filter = new PredicateExpression(dayFilter == myVariable);

for more information see LLBLGen Pro - generated code - Calling a database function.