Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AOT query object datet...
Microsoft Dynamics AX (Archived)

AOT query object datetime range

(0) ShareShare
ReportReport
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

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: AOT query object datetime range

    I wasn't sure if you didn't meant your other range, (ValidUntilDateTime==2016-07-24T16:15:55).

    If "I got the query when saving or compile the query" means that you got the error when saving the query and you tried to use it anyway, then it's indeed not surprising it didn't magically start working.

  • Smartus Profile Picture
    Smartus 620 on at
    RE: AOT query object datetime range

    Well the query where this discussion was about.

    Where I added a range with the value of (getMaxDateTimeValue()).

    When outputting the query, the getMaxDateTimeValue() method was put into place correctly and as you said it did not convert yet.

    When I save this query (and gives me the infolog error) and add it to a view, it gave me a synchronisation error. Which seems quite logical because I got the query when saving or compile the query itself.

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: AOT query object datetime range

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

  • Smartus Profile Picture
    Smartus 620 on at
    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.

  • Verified answer
    Smartus Profile Picture
    Smartus 620 on at
    RE: AOT query object datetime range

    I tried something that works, well sort of.

    6013.range.png

    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

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    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.

  • Smartus Profile Picture
    Smartus 620 on at
    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()?

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    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.

  • Smartus Profile Picture
    Smartus 620 on at
    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

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans