Microsoft Dynamics AX (Archived)

AOT query object datetime range

(0) Share

Report

Posted on by Smartus

620

Hi everyone,

I am creating an AOT query. And in this query i drag some datasources.

I have a field StartDateTime on one of those datasources which should have a fixed datetime value. It's of type utcDateTime.

I tried using the SysQueryRangeUtil class by adding a custom method. The method returns a string and accepts a utcDateTime parameter. In the method I return SysQuery::value(parameter). I also tried without a parameter, simply returning the fixed datetime.

Next I fill the value property of the range with (customMethod(2016-10-19T00:00:00)) but it doesn't work. Invalid range... Infolog message. I also tried the US time notation 10/19/2016 12:00:00 pm, but that doesn't work either.

I also tried adding these datetime values without the custom method. And then the US notation is working. So no problem here, but if I export the query to an AOS which is running on a system with 24h notation regional settings the range value is replaced to double quotes "". If I manually re-enter the same but in 24h notation on that server, it also doesn't complain. But I don't choose what the regional settings are, it should work on all systems independent of those settings.

So what is the right approach here? I didn't find any standard AX queries which have queries with such ranges.

I know I can also create a query in x++ and use the extended range query. But i need this query inside a view as a datasource. Then again I could use a computed column, but I would think that if all these things are possible through coding that they are also possible by creating the AOT query.

And looking on Google it provides me all information about the extended range notation within x++, but not the AOT counterpart.

Hopefully you guys can help me out.

Kind regards,

Vincent Verweij

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (1)

Martin Dráb 237,681 Most Valuable Professional on at

Like (0)

Report

RE: AOT query object datetime range

Using utcDateTime for equality matches sounds suspicious. What if the time will be a second off?

If you actually want match dates (even if stored in utcDateTime), you can get rid of the time part and significantly simplify the whole thing.

I think the key problem is in the fact that you're set the date time value as string (in the property value) and AX must convert it to utcDateTime before passing to your method. Make sure you debug your code to see whether the value you get is what you expect.

If you say that queryValue() returns invalid string even for fixed date time values, that would be a bug, but I wouldn't be that fast in judging. Please try an existing method using queryValue() to convert utcDateTime values, such as greaterThanUtcDate().

I have a few more idea but this should already give you a few things to look at.

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: AOT query object datetime range

Hi Martin,

To give you more background information because you have a valid argument about the time part being one second off.

So the company where I am working for has its own implementation of the ValidFrom and ValidUntil. It's also available in standard AX but this goes back in history with the company itself.

Basically what I am doing is comparing ValidUntilDateTime field (if it's null it's assigned DateTimeUtil::maxValue()) with the outcome of DateTimeUtil::maxValue().

So here is the method itself:

And here is the range being applied:

And the error in the infoLog is this one:

Query extended range failure: Expected identifier near pos 0.

Just a question Martin, how am I able to debug the query? Is it by outputting the query to string or by adding the query to the view and see what it says in SQL?

A colleague tried the exact same method on AX2012R2 and there it works, and I'm on AX2012R3 CU9 and it doesn't ...

Regards,

Vincent

Was this reply helpful? Yes No
Martin Dráb 237,681 Most Valuable Professional on at

Like (0)

Report

RE: AOT query object datetime range

It works all right for me in AX 2012 R3 (but I don't have CU9 there).

Do you get the error immediately when setting the value, or when running the query? Note that you'll get exactly the same error message if the method doesn't exist.

It definitely fails before sending anything to SQL, so that wouldn't see anything there.

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: AOT query object datetime range

Hi Martin,

I get the error when filling in the value property and pressing enter.

But also when I save the query and when I compile the query.

So indeed I wouldn't see anything in SQL.

Because of the error, I also didn't attach it to a view yet.

I compiled and forward-compiled the SysQueryRangeUtil class, and that didn't help.

This error is probably given from kernel code, so I wouldn't be able to debug, would I?

Regards,

Vincent

Was this reply helpful? Yes No
Martin Dráb 237,681 Most Valuable Professional on at

Like (0)

Report

RE: AOT query object datetime range

You can look at the resulting pseudo-SQL query string (using QueryBuildDataSource.toString()), but I don't think you'll see anything interesting there. Then it's indeed interpreted by AX kernel.

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: AOT query object datetime range

It provides me with this output:

...

AND (((getMaxDateTimeValue())))

...

Is that correct or should it translate the DateTimeUtil::maxValue() into 2154-12-31T23:59:59 and fill that in on the place where now is getMaxDateTimeValue()?

Was this reply helpful? Yes No
Martin Dráb 237,681 Most Valuable Professional on at

Like (0)

Report

RE: AOT query object datetime range

It's correct to see getMaxDateTimeValue() there. If it was already interpreted, it would be an equivalent of a static value. The point of these methods is that they're interpreted again on every run, so they can refer to the current date, current user and other things that can change from run to run.

Was this reply helpful? Yes No
Verified answer

Smartus 620 on at

Like (0)

Report

RE: AOT query object datetime range

I tried something that works, well sort of.

This doesn't give me an error, but there's one little sneaky thing about this.

I looked into the AX table browser of the table, and it has a value with that time.

But in the database it's stored in GMT so I should specify the GMT date in the range to make it work as I would like it to work.

But since I need to use the max datetime, which is stored in the database as this: 2154-12-31 23:59:59.000, I can simply put the hard coded value of maxDate.

It's actually weird that I can't specify the value as 2154-12-31T23:59:59, because I retested this and it replaces it with "" as said in the beginning of this post.

Well at least I have found a workaround to this weird thing.

If someone can explain why this acts like it does, feel free to share the explanation.

Regards,

Vincent

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: AOT query object datetime range

Martin,

Although the pseudo-sql query is being shown correctly, when I add this query to a view, it gives me a synchronisation error: View1 - Synchronize database Query extended range failure: Syntax error near 0.

So that doesn't seem to work as we would like.

Was this reply helpful? Yes No
Martin Dráb 237,681 Most Valuable Professional on at

Like (0)

Report

RE: AOT query object datetime range

Which query are you talking about it in your last reply?

Was this reply helpful? Yes No