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

Announcements

No record found.

News and Announcements icon
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,970 Moderator on at

    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 239,084 Most Valuable Professional on at

    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

    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 239,084 Most Valuable Professional on at

    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

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Basit Profile Picture

Basit 1

#1
GL-01081504-0 Profile Picture

GL-01081504-0 1

#1
Roya Profile Picture

Roya 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans