|
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"/>
|
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'
|
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Monday, April 6, 2015
Fetch XML Condition to SQL WHERE
Subscribe to:
Posts (Atom)