using a calculated value in a predicate expression

Posts   
 
    
JayBee
User
Posts: 269
Joined: 28-Dec-2006
# Posted on: 14-Feb-2020 17:21:04   

I am using self servicing, version 4.x.

I have a table with the folowing 3 columns:

 year
 week
 day of week 

I want to do a query where a date is between 2 dates. So I need to calculate a value and compare that with 2 other values.

E.g. suppose I need everything between 01-01-2019 and 31-01-2019. This is equivalent with

 year from = 2019
 week from = 1 
 day from = 2

and

 year to= 2020
 week to = 1 
 day to = 2

In SQL the where clause would be:

WHERE ((1000 * YEAR) + (10 * Week) + Day) between 20190102 and 20200102

How can this be done in a predicate expression?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Feb-2020 06:47:03   

You can use Expressions on predicates (ref...). Example:

var filter = new PredicateExpression();
var specialField = new EntityField("SpecialField", OrderFields.Year * 1000 + OrderFields.Month * 10 + OrderFields.Day);
filter.Add(new FieldBetweenPredicate(specialField, 20190102, 20200102));

var orders = new OrderCollection();
orders.GetMulti(filter);
David Elizondo | LLBLGen Support Team
JayBee
User
Posts: 269
Joined: 28-Dec-2006
# Posted on: 17-Feb-2020 12:35:35   

Thanks!