Complex Query with Date

Dynamics 365 for Finance and Operations/AX Developer Forum
#1

I’m trying to add this query to pull records where two different fields in my table have a date greater than today:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(DBS_SF_AssetView, ArrVerElementModifiedDateTime));

queryBuildRange.value(strFmt(’((%1.%2 > %4) || (%1.%3 > %4))’,

queryBuildDataSource.name(),

fieldStr(DBS_SF_AssetView, ArrVerElementModifiedDateTime),

fieldStr(DBS_SF_AssetView, EndDate),

today()

));

I shouldn’t have any results when i run it for the specific arrangementId because the dates are earlier than today, but I get two results. The actual query select shows the following, which is including the records because it is missing quotes around the actual dates. Any one know how to get this to work correctly, I haven’t found anything online that works?

SQL Select for query:

Query DBS_SF_AssetQuery object 569d9bd0: SELECT * FROM DBS_SF_AssetView(DBS_SF_AssetView_1) WHERE ((((DBS_SF_AssetView_1.ArrVerElementModifiedDateTime > 11/26/2014) || (DBS_SF_AssetView_1.EndDate > 11/26/2014)))) AND (((Status != 5))) AND (((versionStatus != 0))) AND ((DBS_SF_AssetView(DBS_SF_AssetView_1).ArrangementId = N’AG0000000037’))

#2

The query is missing quotes because you haven’t added them. Use queryValue() to convert your date to a valid query range value.

#3

queryvalue(today())

I get “String value cannot be converted to date value in the where clause. Try using the conversion function.” when using queryvalue().

When I try: queryValue(date2str(today(),213,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits2,DateSeparator::Slash,DateYear::Digits4,DateFlags::None))

I get the select without the quotes and same results as original post:

SELECT * FROM DBS_SF_AssetView(DBS_SF_AssetView_1) WHERE ((((DBS_SF_AssetView_1.ArrVerElementModifiedDateTime > 11/15/2014) || (DBS_SF_AssetView_1.EndDate > 11/15/2014)))) AND (((Status != 5))) AND (((versionStatus != 0))) AND ((DBS_SF_AssetView(DBS_SF_AssetView_1).ArrangementId = N’AG0000000037’))

I don’t know what format or ‘conversion functio’ it wants?

#4

queryValue(today())

#5

Thanks for your help.

This is what I’m using, but still getting error:

queryvalue(today())

I get “String value cannot be converted to date value in the where clause. Try using the conversion function.” when using queryvalue().

Any ideas what is wrong?

#6

Here’s my code line:

queryBuildRange.value(strFmt(’((%1.%2 > %4) || (%1.%3 > %4))’,

queryBuildDataSource.name(),

fieldStr(DBS_SF_AssetView, ArrVerElementModifiedDateTime),

fieldStr(DBS_SF_AssetView, EndDate),

queryValue(today())

And this is the select I’m getting in return:

SELECT * FROM DBS_SF_AssetView(DBS_SF_AssetView_1) WHERE ((((DBS_SF_AssetView_1.ArrVerElementModifiedDateTime > “12/2/2014”) || (DBS_SF_AssetView_1.EndDate > “12/2/2014”)))) AND (((Status != 5))) AND (((versionStatus != 0))) AND ((DBS_SF_AssetView(DBS_SF_AssetView_1).ArrangementId = N’AG0000000037’))

#7

some of the code got cut off:

queryBuildRange.value(strFmt(’((%1.%2 > %4) || (%1.%3 > %4))’,

queryBuildDataSource.name(),

fieldStr(DBS_SF_AssetView, ArrVerElementModifiedDateTime),

fieldStr(DBS_SF_AssetView, EndDate),

queryValue(today())

));

#8

All right, I wrote a runnable example to be able to test your issue. If you provided something like that yourself, maybe more people would take a look.

Query query = new Query();
QueryRun queryRun = new QueryRun(query);
QueryBuildDataSource ds = query.addDataSource(tableNum(SalesTable));
    
str rangeStr = strFmt('(%1.%2 < %3)',
        ds.name(),
        fieldStr(SalesTable, CreatedDateTime),
        date2StrXpp(today()));
ds.addRange(fieldNum(SalesTable, CreatedDateTime)).value(rangeStr);
    
info(ds.toString());
    
queryRun = new QueryRun(query);
queryRun.next();

This is the output:

SELECT * FROM SalesTable(SalesTable_1)
WHERE (((SalesTable_1.CreatedDateTime < 03\12\2014)))

You asked how to add “missing quotes” and queryValue() does that, but it doesn’t seems to be a reasonable requirement. You see that there are no quotes in my solution and it works. Use date2strXpp() to convert dates for the purpose of extended query syntax (stick to queryValue() when setting range values normally) and don’t worry about quotes.

#9

I will give that a try. Thanks, I’m still relatively new at this, I’ll provide a runnable example next time…I thought it was maybe just a simple syntax error I was making that wasn’t erroring on save in AX, but also not translating correctly at runtime. I really appreciate your help! I use this forum after searching out on the internet and not finding a solution, but I try. I’m traveling this week, but will try Monday and report back to let others know if it resolved my issue. Thanks again!

#10

The Date2StrXPP() method worked for me. My query now looks similar to this for someone trying to do something similar:

queryBuildRange = queryBuildDataSource.addRange(fieldNum(myTable, ModifiedDateTimeField));

queryBuildRange.value(strFmt(’((%1.%2 > %4) || (%1.%3 > %5))’,

queryBuildDataSource.name(),

fieldStr(myTable, ModifiedDateTimeField),

fieldStr(myTable, EndDateField),

date2StrXpp(myModDateValue),

date2StrXpp(myEndDateValue)

));

Just replace ‘myTable’ with your table name, ‘ModifiedDateTimeField’ and ‘EndDateField’ with your two date fields in your table and then ‘MyModDateValue’ and ‘myEndDateValue’ with the parameter or calculated date values you would like to use to compare the table date fields to.

Hopefully that helps somebody else. Thanks for your help Martin!