CODEWITHSUNDEEP
crmmicrosoft-dynamics
Serhan Altug
Serhan Altug

Reputation: 71

Dynamics CRM month query

How can i query a specific month in Dynamics CRM.

            query = new QueryExpression("personnel") { ColumnSet = new ColumnSet("personnelid", "name", "surname", "birthdate") };

            query.Criteria.AddCondition("birthdate", ConditionOperator.ThisMonth);
            result = Portal.Value.Connection.Value.Service.RetrieveMultiple(query);

This returns only if the birthdate's year is current year.

Upvotes: 2

Views: 2795

Answers (7)

Nick
Nick

Reputation: 755

Query Expression is too complicated. Instead, use fetch xml. You can filter by years, months or fiscal years. Like in the advanced find.

https://msdn.microsoft.com/en-us/library/gg334216.aspx

Upvotes: 0

Nick
Nick

Reputation: 755

There is no way to specify the month. You are limited by the capacity of FetchXML.

Try doing the query using the Advanced Find. In your case I think you could use a combination of two conditions. "Older than X months" and "Next X Months". Then, you can find the X using Today().Months.

Upvotes: 0

C Y
C Y

Reputation: 180

Diaz was giving a good idea; instead of using specific dates, you might want to replace them with dynamics values.

E.g. to query the current month:

 query.Criteria.AddCondition("birthdate", ConditionOperator.GreaterEqual, DateTime.Now);
 query.Criteria.AddCondition("birthdate", ConditionOperator.LessEqual, DateTime.Today.AddDays(1-DateTime.Today.Day));

Upvotes: 1

Lý Thanh Nghị
Lý Thanh Nghị

Reputation: 1

You could use 

  query.Criteria.AddCondition("birthdate", ConditionOperator.Equal,DateTime) ;

Upvotes: -2

Marcelo Acosta
Marcelo Acosta

Reputation: 189

you could use a range to specify the month you want

 query.Criteria.AddCondition("birthdate", ConditionOperator.GreaterEqual, new DateTime(2016,5,1));
 query.Criteria.AddCondition("birthdate", ConditionOperator.LessEqual, new DateTime(2016, 5, 31,23,59,59));

hope it helps

Upvotes: 1

Harinarayanan
Harinarayanan

Reputation: 409

We have faced exactly the same requirement & we found the below solution to help us

  1. create a new field in CRM called "Upcoming birthday"

  2. create a custom workflow/plugin/batch application to calculate the next birthday (code for the same is here http://www.resultondemand.nl/support/sdk/1cff83b0-1f7b-4ddb-a2af-b85f9f785529.htm

  3. Use the same thismonth filter to get the results.

This method has its own drawback as below

  1. Plugin needs a triggering event (which you need to decide according to your business).

  2. Custom workflow - This is most suggested as you can retreive all the contacts which have birthday in last year same day and update it. You can set the custom workflow to timeout and repeat everyday.

  3. Batch Application - This method needs the batch application to be hosted in server.

Upvotes: 0

Guido Preite
Guido Preite

Reputation: 15128

ThisMonth operator means current month, so if you run the query today (2 January 2015) it will return only records with the date as January 2015.

As far as I know there isn't a way to query directly a specific month (I tried also with a LINQ late-bound query but due to the CRM LINQ provider a query like where ((DateTime)c["birthdate"]).Month == 1 doesn't work).

Upvotes: 1

Related Questions