How to format DateTime comparison value in AX Query Service

Question

I am working with the Dynamics AX 2012 R2 query service and need to filter (set a range) on the modifiedDateTime field of the CustTable. I am creating a QueryDataRangeMetadata object and setting its properties. I can filter properly on integer values but not DateTimes.

I was able to figure out that the comparison operator is actually embedded with the value. I have tested this with integer fields and it does work for but I have not been able to figure out how to format a DateTime value so that it is properly evaluated. The code below doesn't work. The range is simply ignored and all records from the CustTable are returned.

    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 = ">2013-02-05T21:17:33Z", // <-- ISSUE: notice the operator with the value!
            Enabled = true
        };

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

        var sort = new QueryDataOrderByMetadata
                       {
                           DataSource = "CustTable",
                           FieldName = "modifiedDateTime",
                           SortOrder = SortOrder.Ascending
                       };

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

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

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

I have also tried these formatting variations with no success:

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

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

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

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

Anyone know what the format of the DateTime is supposed to be in this case?

Answer 1

After iterating over a bunch of DateTime formatting variations I just copied and pasted a value from the UI and guess what? It worked. This is the snippet:

        var range = new QueryDataRangeMetadata
        {
            TableName = "CustTable",
            FieldName = "modifiedDateTime",
            Value = ">2/5/2013 9:17:33 PM",
            Enabled = true
        };

So the format seems to be: comparison_operatorMM/DD/YYYY hh:mm:ss AM

I am in the US and the format is month-first. I imagine that other locales would have to format differently, e.g. day-first.