CAML how tu change Query to get a Range from Date Start, to Date End

Question

I have got a list with the specyfic column

Nazwisko | DataPoc | DataKon |

I'm made a procedure like You told me, but debbuger told me that it's not coming to the loop for.

    string FullQueryUrlap = "<Where><And><Geq><FieldRef Name='{0}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{1}</Value></Geq><And><Leq><FieldRef Name='{2}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{3}</Value></Leq><Eq><FieldRef Name='Nazwisko' /><Value Type='Note'>{4}</Value></Eq></And></And></Where>";
        string dateISO1 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.ToLocalTime());
        Convert.ToDateTime(dateISO1);

        int spacja = DropDownList1.SelectedItem.Text.IndexOf(" ");

        var queryPoszczegolnyUrlop = new SPQuery();
        queryPoszczegolnyUrlop.Query = String.Format(FullQueryUrlap, "DataPoc", dateISO1, "DataKon", dateISO1, DropDownList1.SelectedItem.Text.ToUpper().Substring(0, spacja));
        queryPoszczegolnyUrlop.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='DataPoc' /><FieldRef Name='DataKon' />";
        queryPoszczegolnyUrlop.ViewFieldsOnly = true;

        SPListItemCollection listaDniUrlopow = witryna1.Lists["ListaUrlopow2012"].GetItems(queryPoszczegolnyUrlop);

        DateTime dataPoc= new DateTime();
        DateTime dataKon = new DateTime();

        DateTime theDate = e.Day.Date;

        for (int interator = 0; interator < listaDniUrlopow.Count; interator++)
        {
            SPListItem item = listaDniUrlopow[interator];
            dataPoc = Convert.ToDateTime(item["DataPoc"]);
            dataKon = Convert.ToDateTime(item["DataKon"]);

The debbuger told me that listaDniUrlopow.Count have got 0 elements. Please help. When I giving that query string FullQueryUrlap = "<Where><And><Eq><FieldRef Name='{0}' /><Value Type='DateTime'>{1}</Value></Eq><Eq><FieldRef Name='Nazwisko' /><Value Type='Text'>{2}</Value></Eq></And></Where>"; it count me one element which show "dataPoc" value.

Please Help I create a line with no parameters, and I made it by U2U it's work great:

 <Where>
  <And>
     <Geq>
        <FieldRef Name='DataPoc' />
        <Value IncludeTimeValue='TRUE' Type='DateTime'>2012-11-01T10:36:47Z</Value>
     </Geq>
     <And>
        <Leq>
           <FieldRef Name='DataKon' />
           <Value IncludeTimeValue='TRUE' Type='DateTime'>2012-11-30T10:37:05Z</Value>
        </Leq>
        <Eq>
           <FieldRef Name='Nazwisko' />
           <Value Type='Note'>ZAWISTOWSKI</Value>
        </Eq>
     </And>
  </And>

Its shows my all data on range, but I don't know how to send dateISO1. I think the main problem is this line:

 queryPoszczegolnyUrlop.Query = String.Format(FullQueryUrlap, "DataPoc", dateISO1, "DataKon", dateISO1, DropDownList1.SelectedItem.Text.ToUpper().Substring(0, spacja));

Two times there is dateISO1, and I don't know how tu send there secound date. The Query with parameters now I sending is: string FullQueryUrlap = "<Where><And><And><Geq><FieldRef Name='{0}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{1}</Value></Geq><Leq><FieldRef Name='{2}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{3}</Value></Leq></And><Eq><FieldRef Name='Nazwisko' /><Value Type='Note'>{4}</Value></Eq></And></Where>"; The result of Your code:

string dateISO1 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.AddDays(-1).ToLocalTime()); Convert.ToDateTime(dateISO1); string dateISO2 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.AddDays(1).ToLocalTime()); Convert.ToDateTime(dateISO2); ... queryPoszczegolnyUrlop.Query = String.Format(FullQueryUrlap, "DataPoc", dateISO1, "DataKon", dateISO2, DropDownList1.SelectedItem.Text.ToUpper().Substring(0, spacja));

is:

And The list haven't got a element 1 November, look at the screen:

Edited: Describing what I want

As You can see at last screen shot i have three time of vacation. All is on November 2012. I want to color the calendar on this ange of data. Betwen Data Start, and Data Stop. So it shouldyn't be only two days, but more, like on the list there is First of all it should color me 2012-11-02 and colored it, next should color me from 2012-11-06 to the 2012-11-15, and next it should color me third range from 2012-11-13 to the 2012-11-28, day by day, please help me, how should i do this ranges.

Here is All my code responsible of render Vacation:

string FullQueryUrlap = "<Where><And><Geq><FieldRef Name='{0}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{1}</Value></Geq><And><Leq><FieldRef Name='{2}' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{3}</Value></Leq><Eq><FieldRef Name='Nazwisko' /><Value Type='Note'>{4}</Value></Eq></And></And></Where>";
        string dateISO1 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.AddDays(-1).ToUniversalTime().AddHours(2.0));
        Convert.ToDateTime(dateISO1);
        string dateISO2 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.AddDays(1).ToUniversalTime().AddHours(1.0));
        Convert.ToDateTime(dateISO2);

        int spacja = DropDownList1.SelectedItem.Text.IndexOf(" ");

        var queryPoszczegolnyUrlop = new SPQuery();
        queryPoszczegolnyUrlop.Query = String.Format(FullQueryUrlap, "DataPoc", dateISO1, "DataKon", dateISO2, DropDownList1.SelectedItem.Text.ToUpper().Substring(0, spacja));
        queryPoszczegolnyUrlop.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='DataPoc' /><FieldRef Name='DataKon' />";
        queryPoszczegolnyUrlop.ViewFieldsOnly = true;

        SPListItemCollection listaDniUrlopow = witryna1.Lists["UrlopyTestowa"].GetItems(queryPoszczegolnyUrlop);

        DateTime dataPoc= new DateTime();
        DateTime dataKon = new DateTime();

 //       dataPoc = Calendar1.TodaysDate;
 //       dataKon = dataPoc.AddDays(5.0);

 //       if ((e.Day.Date >= dataPoc) &&
 //(e.Day.Date <= dataKon))
 //       {
 //           e.Cell.Style.Add("background-color", "#0000FF");
 //       }

        DateTime theDate = e.Day.Date;

        for (int interator = 0; interator < listaDniUrlopow.Count; interator++)
        {
            SPListItem item = listaDniUrlopow[interator];
            dataPoc = Convert.ToDateTime(item["DataPoc"]);
            dataKon = Convert.ToDateTime(item["DataKon"]);


            while (dataPoc <= dataKon)
            {


                    e.Cell.Style.Add("background-color", "#FF00FF");
                    e.Cell.CssClass = "dni_swiateczne";
                    e.Cell.Controls.Add(new LiteralControl(("<p style='color:grey; font-size:9px;'>" + item["Title"].ToString() + "</p>" + "<p style='color:#000000; font-size:8px;'>" + dataPoc.ToString().Substring(0, 10) + "</p>")));
                    //Calendar1.SelectedDates.Add(dataPoc);
                    //Calendar1.SelectedDayStyle.CssClass = "dni_swiateczne";
                    //Calendar1.SelectedDayStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#FF00FF");
                    //Calendar1.SelectedDayStyle.ToString();



                dataPoc = dataPoc.AddDays(1.0);


            }
        }

Edit Update: I want to present on calendar dates of vacation workers in my firm. The vacation will be on the list: List is:

In Polish: Nazwisko | DataPoc | DataKon |

In English: Surname |DataStart| DataStop|

So I take one person, me to try script, and put me, and my vacation to the list:

To easy to see I put all to the one mounth - November. I want to color a time of vacation, day by day, so I need dataISO1 and dataISO2 to color from Date Start to Date End, each time when e vacation begins.

As You can se in this list there is 3 vacations. First 2012-11-02, Secound from 2012-11-06 to the 2012-11-15 and third 2012-11-13 to the 2012-11-28 day by day should Calendar be colored

Answer 1

I would of built this query like this, instead of your variables I have left in the formats for you to look at. This should work, if not then you are probably looking at the note field incorrectly if the date ranges are correct.

Also you are using a date range, with the same date time, also restricting to time, so unless the list item was made at that exact date and time you will get no results. To view this just print out your caml after the string format and look at it. The two date times will be identical. You will need to either or both of subtracting from the lower date or adding to the greater date with AddDays(positive/negative value).

Also check that your drop down isn't just looking for part of your note field, at the moment the whole field must match, perhaps you want to use Contains not Eq?

<Query>
   <Where>
      <And>
         <And>
            <Geq>
               <FieldRef Name='DataKon' />
               <Value IncludeTimeValue='TRUE' Type='DateTime'>[date here]</Value>
            </Geq>
            <Leq>
               <FieldRef Name='DataPoc' />
               <Value IncludeTimeValue='TRUE' Type='DateTime'>[date here]</Value>
            </Leq>
         </And>
         <Eq>
            <FieldRef Name='Nazwisko' />
            <Value Type='Note'>{4}</Value>
         </Eq>
      </And>
   </Where>
</Query>

[update]

Example modification:

string dateISO1 = SPUtility.CreateISO8601DateTimeFromSystemDateTime(e.Day.Date.ToLocalTime());
Convert.ToDateTime(dateISO1);
...
queryPoszczegolnyUrlop.Query = String.Format(FullQueryUrlap, "DataPoc", dateISO1, "DataKon", dateISO1, DropDownList1.SelectedItem.Text.ToUpper().Substring(0, spacja));