Unable to get date range to work in AX 2012 R2 Query Services from C# app

This question has suggested answer(s)

Hi all,

I have been trying to figure out how to query a table and filter based on a DateTime field with a 'greater than' comparison operator

I am running the code below as a test and I can get an individual record when the supplied DateTime matches (equality operator.) But how would you specify other kinds of comparison operators such as greater than, not equal to, less than or equal to etc.

      
public static void RangeTest()
{
    var client = new QueryServiceClient();

    var dataSource = new QueryDataSourceMetadata
    {
        Table = "CustTable",
        Name = "CustTable",
        HasRelations = false,
        Enabled = true,
        DynamicFieldList = true // get all fields
    };

    var range = new QueryDataRangeMetadata
    {
        TableName = "CustTable",
        FieldName = "modifiedDateTime",
        Value = "1/17/2013 9:51:22 PM",
        Enabled = true
    };

    dataSource.Ranges = new QueryRangeMetadata[] { range };

    var query = new QueryMetadata
    {
        QueryType = QueryType.Join,
        DataSources = new[] { dataSource }
    };

    Paging paging = null;
    var dataSet = client.ExecuteQuery(query, ref paging);

    Console.WriteLine(dataSet.Tables[0].Rows.Count);
}

      

All Replies
  • I have figured out from this link: books.google.com/books that the comparison operator actually has to be included with the value, like this:

    var range = new QueryDataRangeMetadata

    {

       TableName = "CustTable",

       FieldName = "RecId",

       Value = ">5637145328",

       Enabled = true

    };

    This snippet above works for comparing integer fields but what format should should the DateTime take?

    I have tried these variations with no success:

    FieldName = "modifiedDateTime",

    Value = ">2013-02-05T9:17:33"

    Value = ">'2013-02-05T9:17:33'"

    Value = ">2013-02-05T21:17:33Z"

  • check by debugging the query from x++ code.

    query q = new query (querystr(TestQuery));

    add a rabge on this and set the value.

    while debugging point it on the query object you will get the actual query you are using .Replicate the same

  • var custRange = new QueryDataRangeMetadata

               {

                   TableName = "CustTable",

                   FieldName = "modifiedDateTime",

                   Value = ">10/24/2013 1:05:00 PM",

                   Enabled = true

               };