Fetch XML Condition to SQL WHERE


Advanced Find Operator
Fetch XML Operator
FetchXML Filter
SQL Where condition
Equal
eq
<condition attribute="firstname"
value="John" operator="eq"/>
WHERE firstname = 'John'
Not Equal
ne
<condition attribute="firstname"
value="John" operator="ne"/>
WHERE firstname != 'John'
Contains
like
<condition attribute="firstname"
value="John" operator="like"/>
WHERE firstname LIKE '%John%'
Does Not Contains
not-like
<condition attribute="firstname"
value="John" operator="not like"/>
WHERE firstname NOT LIKE '%John%'
Begins with
like
<condition attribute="firstname"
value="John%" operator="like"/>
WHERE firstname LIKE 'John%'
Does Not Begins with
not-like
<condition attribute="firstname"
value="John%" operator="not like"/>
WHERE firstname NOT LIKE 'John%'
Ends with
like
<condition attribute="firstname"
 value="%John" operator="like"/>
WHERE firstname LIKE '%John'
Does Not End with
not-like
<condition attribute="firstname"
value="%John" operator="not like"/>
WHERE firstname NOT LIKE '%John'
Contains Data
not-null
<condition attribute="firstname"
operator="not-null"/>
WHERE firstname IS NOT NULL
Does Not Contains Data
null
<condition attribute="firstname"operator="null"/>
WHERE firstname IS NULL
Is Greater than
gt
<condition attribute="annualincome"
value="123" operator="gt"/>
WHERE AnnualIncome >123
Is Greater than or Equal To
ge
<condition attribute="annualincome"
 value="123" operator="ge"/>
WHERE AnnualIncome >=123
Is Less than
lt
<condition attribute="annualincome"
value="123" operator="lt"/>
WHERE AnnualIncome <123 span="">
Is less than or Equal To
le
<condition attribute="annualincome"
value="123" operator="le"/>
WHERE AnnualIncome <=123
Equals Current User
eq-userid
<condition attribute="createdby"
operator="eq-userid"/>
WHERE CreatedBy ='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
Does Not Equal Current user
ne-userid
<condition attribute="createdby"
operator="ne-userid"/>
WHERE CreatedBy !='7BC449A8-5A8F-E311-A63C-0050568C6D7D'
Equals Current User' Teams
eq-userteams
<condition attribute="ownerid"
operator="eq-userteams"/>
WHERE OwnerId IN ('7BC449A8-5A8F-E311-A63C-0050568C6D7D')
Equals Current user Or User's Teams
eq-useroruserteams
<condition attribute="ownerid"
operator="eq-useroruserteams"/>
WHERE OwnerId IN('7BC449A8-5A8F-E311-A63C-0050568C6D7D',' 09C5427D-88B7-E411-8BFB-0050568C6D7D')
On
on
<condition attribute="createdon"
value="2015-04-01" operator="on"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
On or After
on-or-after
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-after"/>
WHERE CreatedOn >= '2015-04-01 05:00:00'
On or Before
on-or-before
<condition attribute="createdon"
value="2015-04-01"
operator="on-or-before"/>
WHERE CreatedOn < '2015-04-02 05:00:00' 
Yesterday
yesterday
<condition attribute="createdon"operator="yesterday"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-04-02 05:00:00'
Today
today
<condition attribute="createdon"operator="today"/>
WHERE CreatedOn >= '2015-04-02 05:00:00' AND CreatedOn < '2015-04-03 05:00:00'
Tomorrow
tomorrow
<condition attribute="createdon"operator="tomorrow"/>
WHERE CreatedOn >= '2015-04-03 05:00:00' AND CreatedOn < '2015-04-04 05:00:00' 
Next 7 Days
next-seven-days
<condition attribute="createdon"operator="next-seven-days"/>
WHERE CreatedOn >= '2015-04-02 21:25:41.110' AND CreatedOn <'2015-04-10 05:00:00'
Last 7 Days
last-seven-days
<condition attribute="createdon"operator="last-seven-days"/>
WHERE CreatedOn >= '2015-03-26 05:00:00' AND CreatedOn <= '2015-04-02 21:25:41.110'
Next Week
next-week
<condition attribute="createdon"operator="next-week"/>
WHERE CreatedOn >= '2015-04-05 05:00:00' AND CreatedOn < '2015-04-12 05:00:00' 
Last Week
last-week
<condition attribute="createdon"operator="last-week"/>
WHERE CreatedOn >= '2015-03-22 05:00:00' AND CreatedOn < '2015-03-29 05:00:00'
This Week
this-week
<condition attribute="createdon"operator="this-week"/>
WHERE CreatedOn >= '2015-03-29 05:00:00' AND CreatedOn < '2015-04-05 05:00:00'
Next Month
next-month
<condition attribute="createdon"operator="next-month"/>
WHERE CreatedOn >= '2015-05-01 05:00:00' AND CreatedOn < '2015-06-01 05:00:00' 
Last Month
last-month
<condition attribute="createdon"operator="last-month"/>
WHERE CreatedOn >= '2015-03-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
This Month
this-month
<condition attribute="createdon"operator="this-month"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-05-01 05:00:00' 
Next Year
next-year
<condition attribute="createdon"operator="next-year"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00' 
Last Year
last-year
<condition attribute="createdon"operator="last-year"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00'
This Year
this-year
<condition attribute="createdon"operator="this-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00' 
Last X Hours
last-x-hours
<condition attribute="createdon"
value="1" operator="last-x-hours"/>
WHERE CreatedOn >= '2015-04-02 20:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
Next X Hours
next-x-hours
<condition attribute="createdon"
value="1" operator="next-x-hours"/>
WHERE CreatedOn >= '2015-04-02 21:53:49.933' AND CreatedOn <'2015-04-02 23:00:00' 
Last X Days
last-x-days
<condition attribute="createdon"
 value="1" operator="last-x-days"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn <= '2015-04-02 21:53:49.933'
Next X Days
next-x-days
<condition attribute="createdon"
 value="1" operator="next-x-days"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.257' AND CreatedOn <'2015-04-05 05:00:00'
Last X Weeks
last-x-weeks
<condition attribute="createdon"
value="1" operator="last-x-weeks"/>
WHERE CreatedOn >= '2015-03-27 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X Weeks
next-x-weeks
<condition attribute="createdon"
value="1" operator="next-x-weeks"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2015-04-11 05:00:00' 
last X Months
last-x-months
<condition attribute="createdon"
 value="1" operator="last-x-months"/>
WHERE CreatedOn >= '2015-03-03 06:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X months
next-x-months
<condition attribute="createdon"
value="1" operator="next-x-months"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2015-05-04 05:00:00' 
Last X Years
last-x-years
<condition attribute="createdon"
value="1" operator="last-x-years"/>
WHERE CreatedOn >= '2014-04-03 05:00:00' AND CreatedOn <= '2015-04-03 19:48:07.260'
Next X Years
next-x-years
<condition attribute="createdon"
value="1" operator="next-x-years"/>
WHERE CreatedOn >= '2015-04-03 19:48:07.260' AND CreatedOn <'2016-04-04 05:00:00' 
Any Time
not-null
<condition attribute="createdon"
operator="not-null"/>
WHERE CreatedOn IS NOT NULL
Older than x months
olderthan-x-months
<condition attribute="createdon"
value="1" operator="olderthan-x-months"/>
WHERE CreatedOn < '2015-03-03 06:00:00'
In Fiscal Year
in-fiscal-year
<condition attribute="createdon"
value="2015" operator="in-fiscal-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
In Fiscal Period
in-fiscal-period
<condition attribute="createdon"
value="1" operator="in-fiscal-period"/>
WHEREdbo.fn_GetFiscalPeriod('2014-01-01 00:00:00', 4, CreatedOn, '20') = 1
In Fiscal Period and Year
in-fiscal-period-and-year
<condition attribute="createdon=" 
operator="in-fiscal-period-and-year=">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
In or After Fiscal Period
in-or-after-fiscal-period-and-year
<condition attribute="createdon" 
operator="in-or-after-fiscal-period-and-year">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn >= '2015-01-01 06:00:00'
In or Before Fiscal Period
in-or-before-fiscal-period-and-year
<condition attribute="createdon" 
operator="in-or-before-fiscal-period-and-year">
  <value>01</value>
  <value>2015</value>
</condition>
WHERE CreatedOn < '2015-04-01 05:00:00' 
Last Fiscal Year
last-fiscal-year
<condition attribute="createdon"operator="last-fiscal-year"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00' 
This  Fiscal Year
this-fiscal-year
<condition attribute="createdon"operator="this-fiscal-year"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2016-01-01 06:00:00'
Next  Fiscal Year
next-fiscal-year
<condition attribute="createdon"operator="next-fiscal-year"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00'
Last X  Fiscal Year
last-x-fiscal-years
<condition attribute="createdon"
value="1" operator="last-x-fiscal-years"/>
WHERE CreatedOn >= '2014-01-01 06:00:00' AND CreatedOn < '2015-01-01 06:00:00' 
Next X  Fiscal Year
next-x-fiscal-years
<condition attribute="createdon"
value="1" operator="next-x-fiscal-years"/>
WHERE CreatedOn >= '2016-01-01 06:00:00' AND CreatedOn < '2017-01-01 06:00:00' 
Last Fiscal Period
last-fiscal-period
<condition attribute="createdon"operator="last-fiscal-period"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00' 
This Fiscal Period
this-fiscal-period
<condition attribute="createdon"operator="this-fiscal-period"/>
WHERE CreatedOn >= '2015-04-01 05:00:00' AND CreatedOn < '2015-07-01 05:00:00'
Next Fiscal Period
next-fiscal-period
<condition attribute="createdon"operator="next-fiscal-period"/>
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00' 
Last X Fiscal Period
last-x-fiscal-periods
<condition attribute="createdon"
value="1" operator="last-x-fiscal-periods"/>
WHERE CreatedOn >= '2015-01-01 06:00:00' AND CreatedOn < '2015-04-01 05:00:00'
Next X Fiscal Period
next-x-fiscal-periods
<condition attribute="createdon"
value="1" operator="next-x-fiscal-periods"/>
WHERE CreatedOn >= '2015-07-01 05:00:00' AND CreatedOn < '2015-10-01 05:00:00'

Comments

  1. Our amazing CRM for tour operators travel agent software helps Cruises, Tour Operators, Safaris & Bespoke holiday sellers. Manage the customer journey & Gain up to 30% efficiency!It helps Tour Operators in promoting their travel products, increase sales, organise day to day operations and reduce admin cost.Travel Agency CRM is crafted for travel agencies and tour operators on award-winning Zoho CRM. Get started with a no-obligation demonstration.

    ReplyDelete

Post a Comment

Popular posts from this blog

Workflow Activity Input and Output Parameters and Data Types in MS CRM 2013/15/16

Get Workflow Guid in Javascript using OData query

Convert Plugin to Sandbox mode,