Date range conditions and between operator in AX 2012

I faced some logical issues when I work with the between ranges. So, I just like to share my code logic for your references.

Between operators

AX does not support between operator in select statement. But it is mostly using operator in SQL queries.

For example, find the persons between the age 20 to 30, we can use between operator like below statement

SELECT * FROM Persons WHERE Age BETWEEN 20 AND 30

The above SQL query return the result set that persons between the age 20 and 30.


In AX, we don’t have other options. So, we need to use the basic logic like the below statement

select * from persons where persons.age >= 20 && persons.age <= 30;

Date range:

We can find the date is in the particular date range by using the below statement. For example, if we need to find, what are the courses conducted in the particular date. We can use the below statement.

select * from courses where today() >= courses.startdate && today() <= courses.enddate;

Date ranges overlap:

It is little difficult logic. Find the date range is match with the other date range. For example, find the particular week active employees. (In other words, find the active employee list for the particular week)

select * from employees where employees.validfromdate <= endate && startdate <= employees.validtodate;