web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Advanced query for dates
Microsoft Dynamics AX (Archived)

Advanced query for dates

(0) ShareShare
ReportReport
Posted on by DaxNigel 2,574

I am building a view, which contains a datasource that have a fromdate and todate field. In the view I only want to display the valid record from the fromdate and todate combination.

I have tried to enter in the query value on the view the advanced query ((fromdate <= today()) && (todate >= today()))

The system allows the value, but when I synchronise the view I get the following error:

String value cannot be converted to date value in the where clause. Try using the conversion function.

How do I achieve applying the advanced query to filter the records to just return the one valid record based on the two fields against todays date.

*This post is locked for comments

I have the same question (0)
  • Guy Terry Profile Picture
    Guy Terry 28,901 Moderator on at
    RE: Advanced query for dates

    Is the datasource a custom table? My suggestion would be to look at making this a 'Date effective' or 'ValidTimeState' table. I understand it is 'easy' to get the current record from this type of table, whilst also maintaining historical/future data.

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 236,394 Most Valuable Professional on at
    RE: Advanced query for dates

    A view basically is a piece of T-SQL code; X++ methods such as today() have no meaning there.

    I suggest you simply expose the date in your view and then use a query in AX to filter them by the current date at the time of execution.

  • DaxNigel Profile Picture
    DaxNigel 2,574 on at
    RE: Advanced query for dates

    Hi Martin,

    That is not completely correct. You can build a view and add a query control and use today(), that works fine, my problem is I cannot seem to use it in an advanced query setup.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 236,394 Most Valuable Professional on at
    RE: Advanced query for dates

    You can do it, but the result won't be what you want. There won't be any call to X++ from SQL Server when the view runs; the method will be interpreted at synchronization and you'll get a hard-coded date in T-SQL.

    For example, you create a view today, therefore today() will run and 12.1.2018 will be written into the view definition in database. If you run the view a few days later, you'll still get values for 12.1.2018. But you wanted the current date, didn't you?

    When debugging views, it's very useful to look at the actual view definition in database (through SQL Server Management Studio).

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans