Retrieve List items using CAML Query

Question

I have a list which contains 3 columns -ID, TicketState and EndDate

ID        TicketState           EndDate(DD-MM-YYYY)

1             Open            10/10/2012
2            Closed           01/12/2012 
3           Discard           05/05/2011
4             Open            28/05/2013
5           Discard           10/11/2012

So, I want to retrieve those items whose TicketState is Either 
Closed OR Discard, 
AND
EndDate < 1 year (from todays Date)

For that i have written following CAML Query but it is not working....

SPQuery myquery = new SPQuery();
    myquery.Query = "<Where>" +
                       "<And>" +
                           "<Or>" +
                              "<Eq><FieldRef Name='TicketState' /><Value Type='Choice'>Closed</Value></Eq>" +
                              "<Eq><FieldRef Name='TicketState' /><Value Type='Choice'>Discard</Value></Eq>" +      
                           "</Or>" +
                         "<Geq><FieldRef Name='End_x0020_Time' /><Value Type='DateTime' IncludeTimeValue='FALSE'><Today Offset='-365' /></Value></Geq>" +
                       "</And>" +
                    "</Where>";

EDIT:

Solved. Problem was with the internal name.

What's the output your getting. In your sample list there seems to be no date ranging within one year. Are you getting any output ? — Shaamil, Sep 10 '14 at 09:30

score 1 · Answer 1 · answered Sep 10 '14 at 09:51

1

Solved...Problem was with the internal name....

Thank you..

answered Sep 10 '14 at 09:51

Pranav Bilurkar

293
3
5
18

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – Benny Skogberg Sep 10 '14 at 10:53

score 0 · Answer 2 · edited Apr 13 '17 at 12:41

0

You are using <Eq> with End_x0020_Time column, instead you should be using <Geq> or <Leq> as per your need..

Please have a look at:

Using Date offset in CAML query

edited Apr 13 '17 at 12:41

Community

1

answered Sep 10 '14 at 09:25

Arsalan Adam Khatri

14,531
3
36
59

used ..still no output and i hav data ranging within the dates – Pranav Bilurkar Sep 10 '14 at 09:35
If you want 1 year difference, why are you giving -100 as Offset. You should be giving -365 as Offset, isn't it ? – Arsalan Adam Khatri Sep 10 '14 at 09:38
If you are working only on the data that you have mentioned in the post you wont get any results. Is there any other relevant data. Are you using the CAML U2U builder ? If yes, are you getting any results in the tool ? – Vipul Kelkar Sep 10 '14 at 09:38
Offset= -365 done, still no output – Pranav Bilurkar Sep 10 '14 at 09:47

Retrieve List items using CAML Query

2 Answers2