DateRange purgatory

Posts   
 
    
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 11-Jun-2004 10:46:59   

Hi

I have a SQL Server table with 2 fields (smalldatetime): ArrivalDate & DepartureDate

What I need to do is return ONLY (the key word!!) the records in the table where a date parameter is greater than or equal to ArrivalDate OR the date parameter is less than the DepartureDate.

Sounds simple on paper but I'm having the hardest time tying to get this to work using the predicate expression factory.

I have a feeling I need to be using the FieldCompareRangePredicate, but, seeing as there are no examples of how to use this in the help, I was hoping someone could help me out on this one..

TIA Steve

uydo
User
Posts: 43
Joined: 09-Dec-2003
# Posted on: 11-Jun-2004 17:00:30   

Hi Steve, You can do something like this: 1. Create a separate predicate expression for the ArrivalDate & DepatureDate only:


IPredicateExpression filterCompareDate = new PredicateExpression();
filterCompareDate.Add(PredicateFactory.CompareValue(<Table>FieldEntity.ArrivalDate, ComparisonOperator.LessThanEqual, ArrivalDate1));
filterCompareDate.AddWithOr(PredicateFactory.CompareValue(<Table>FieldIndex.DepartureDate, ComparisonOperator.GreaterThan, DepatureDate1));

  1. Then add this predicate into your main predicate!.

IPredicateExpression filter = new PredicateExpression();
//filter.Add(PredicateFactory.CompareValue(<Table>FieldEntity.a, ComparisonOperator.Equal, b));;
filter.AddWithAnd(filterCompareDate);

This code generates something like this:

select ... from... where a=b and (<Table>.ArrivalDate LTE ArrivalDate1 OR <Table>.DepartureDate GT DepartureDate1)

I see there is a good example in the the doc that explains predicate expression very well.

HTH, Uy

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 11-Jun-2004 17:21:10   

Also take into account that SqlServer stores the time with date values, always. So if you want to test for a given date, you have to test with BETWEEN using as time 00:00 and 23:59.59

Frans Bouma | Lead developer LLBLGen Pro
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 11-Jun-2004 19:21:14   

Thx Otis - I'll give it a go!

Steve

PS Yes, I did see the example in the help. However, I was (in my project) off on another tangent trying stuff. Just needed a push in the right direction is all...