Skip to main content

Notifications

Announcements

Announcing Our 2025 Season 1 Super Users!
Announcing Forum Attachment Improvements!
News and Announcements icon
Dynamics 365 Community / Blogs / MS CRM Customization / Fetch XML Condition to SQL ...

Fetch XML Condition to SQL WHERE

Mahadeo Matre Profile Picture Mahadeo Matre 17,021
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
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"/>
WHERE dbo.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

*This post is locked for comments