1

  • 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.

|improve this question
  • Is the field name "End%5Fx0020%5FTime" correct ? – Arsalan Adam Khatri Sep 10 '14 at 9:17
  • i have edited..check it out – Pranav Bilurkar Sep 10 '14 at 9:20
  • 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 9:30
  • No Output....i think problem with caml query itself.. – Pranav Bilurkar Sep 10 '14 at 9:34
  • Solved...Problem was with the internal name.... Thank you.. – Pranav Bilurkar Sep 10 '14 at 9:51
1

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

Thank you..

|improve this answer
  • 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
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

|improve this answer
  • used <Geq>..still no output and i hav data ranging within the dates – Pranav Bilurkar Sep 10 '14 at 9: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 9: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 9:38
  • Offset= -365 done, still no output – Pranav Bilurkar Sep 10 '14 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.