Trouble with date ranges 1

[rmttbone]

I am using Crystal Report 10 and an Informix and SQL Server DB.

I need to obtain records that are active between 7-1-05 and 12-31-05.

I have a field for the start date and one for the end date.

I attempted to use this select statement:

{start_date}>= Date (2005,7,1) and
{end_date}<= Date (2005,12,31)

but, I am excluding several records that are in this range but have a start or end date outside the range., e.g., a record with a start_date of 6-1-05 and end_date of 1-2-06

 

[lbass]

Change your formula to:

{start_date} < date(2006,1,1) and
(
isnull({end_date}) or
{end_date} >= Date (2005,7,1)
)

-LB