Select with Timestamp (ValidDateTime)

(0) Share

Report

Posted on by Yuji

4,131

Hi,

If I have a start date time and end date time, would like to get the active HCMEmployment record during that date range in Query, how can I write it in X++?

Thanks,

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Suggested answer

István Orosz 2,137 on at

Like (1)

Report
Copy link

Link copied!

Hi,

You can refer to : Valid Time State Tables and Date Effective Data usage at msdn.microsoft.com/.../gg861781.aspx.

Your query should look something like this:

select validTimeState(validFrom, validTo) . . .

from HcmEmployment . . .

Kind Regards,

István

Was this reply helpful? Yes No
Suggested answer

Zain Bokhari 3,208 Moderator on at

Like (1)

Report
Copy link

Link copied!
You can simply google it, the first link gives you the answer.

To quote the example from Microsoft Library.

static void VtsJob1(Args _args) { // A VTS table in AX 2012. CustPackingSlipTransHistory xrec1; utcDateTime myDateFrom , myDateTo; anytype myAnytype = -1; myDateFrom = DateTimeUtil::utcNow(); myDateTo = myDateFrom; SELECT validTimeState(myDateFrom, myDateTo) * FROM xrec1; myAnytype = xrec1.getFieldValue("RecId"); info(myAnytype); }

Was this reply helpful? Yes No
Yuji 4,131 on at

Like (0)

Report
Copy link

Link copied!

Thanks István,

But I am using Query, QueryRun and QueryBuildRange, not pure X++ statement.

thanks,

Was this reply helpful? Yes No
Suggested answer

Zain Bokhari 3,208 Moderator on at

Like (0)

Report
Copy link

Link copied!

Here is how you can use Query with ValidTimeState tables. Microsoft library pages also gives you an example.

Was this reply helpful? Yes No
Basheer17 2,720 on at

Like (0)

Report
Copy link

Link copied!

For better understanding of this framework, download the Whitepaper Using_Date_Effective_Patterns_AX2012.pdf from www.microsoft.com/.../details.aspx.

Was this reply helpful? Yes No
Verified answer

István Orosz 2,137 on at

Like (0)

Report
Copy link

Link copied!

Hi,

You can use a similar solution to this:

qry = new Query();

....

qry.validTimeStateDateTimeRange(validFrom, validTo);

....

Regards,

István

Was this reply helpful? Yes No
Hassan EL Alama 55 on at

Like (0)

Report
Copy link

Link copied!

Hi,

Not forget validTimeStateUpdate :

logisticsPostalAddressHeader_ds.validTimeStateUpdate(ValidTimeStateUpdate::Correction);

logisticsPostalAddressHeader_ds.query().validTimeStateDateTimeRange(DateTimeUtil::minValue(), DateTimeUtil::maxValue());

Regards

Hassan

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Copy link

Link copied!
Hi Yuji,

you can use SysQuery::Range(), below is the example code:

TransDate dateFrom; TransDate dateTo; Query qry = new Query(); QueryBuildDataSource qbds1; ; ... ... qbds1.addRange(fieldNum(myTable,myField)).value(SysQuery::Range(dateFrom, dateTo));

Regards

Was this reply helpful? Yes No