How to setup system date in a query

Finance and Operations/AX Developer Forum
1

I need some help writing a query…

What I need is 'Select priceDicTable where PriceDiscTable.ItemRelation == mbsbdVendPriceIncreaseLines.ItemID && PriceDiscTable.AccountRelation == mbsbdVendPriceIncreatseLines.AccountNum && PriceDiscTable.FromDate >= sysdate() && PriceDiscTable.ToDate <= sysdate()

I’m having a hard time figuring out how to setup the >= sysdate.

What I have so far is …

query = new Query();

qbds = query.addDataSource(tablenum(priceDiscTable));
qbds.addRange(fieldnum(priceDiscTable,ItemRelation)).value(queryValue(mbsbdVendPriceIncreaseLines.ItemID));
qbds.addRange(fieldnum(priceDiscTable,AccountRelation)).value(queryValue(mbsbdVendPriceIncreaseLines.AccountNum));

I need to setup another range statement to include the sysdates.

2

Hi,

try this:

qbds.addRange(fieldnum(priceDiscTable,FromDate)).value(queryRange(systemdateget(), datemax())));

qbds.addRange(fieldnum(priceDiscTable,ToDate)).value(queryRange(datenull(), systemdateget())));

I don’t want to log on to a system tonight to proof it, but I guess it should be ok.

Also are you sure that this is correct: PriceDiscTable.FromDate >= sysdate() && PriceDiscTable.ToDate <= sysdate() ? Whether I might have had too much wine tonight, or I don’t get what you want to achieve with such date range (FromDate is in the future, ToDate is in the past… weird. Most likely with such date range you will only select records that have both From- and ToDate equal with today’s date). Do you really have one day prices in system?

Janis.