Question Status

Suggested Answer
psjr asked a question on 1 Mar 2013 1:41 PM

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);
}

      

Reply
psjr responded on 1 Mar 2013 2:04 PM

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"

Reply
venkatesh vadlamani responded on 3 Mar 2013 1:25 AM

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

Reply
Suggested Answer
Haim responded on 25 Oct 2013 11:08 AM

var custRange = new QueryDataRangeMetadata

           {

               TableName = "CustTable",

               FieldName = "modifiedDateTime",

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

               Enabled = true

           };

Reply
Suggested Answer
Haim responded on 25 Oct 2013 11:08 AM

var custRange = new QueryDataRangeMetadata

           {

               TableName = "CustTable",

               FieldName = "modifiedDateTime",

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

               Enabled = true

           };

Reply