Caml Query to get the last 30 days data

Question

I want to fetch the last 30 days items from a sharepoint list.

My effort so far,

                        SPQuery dataColQuery = new SPQuery();

                        dataColQuery.Query = "<Where>"+
                                                "<And>"+
                                                    "<Eq>"+
                                                        "<FieldRef Name='isAct' /><Value Type='Choice'>Yes</Value>"+
                                                    "</Eq>"+
                                                    "<Geq>"+
                                                        "<FieldRef Name='Created' /><Value Type='DateTime' IncludeTimeValue='FALSE'><Today Offset='-30' /></Value>"+
                                                    "</Geq>"+
                                                "</And>"+
                                              "</Where>"+
                        "<OrderBy><FieldRef Name='Created' Ascending='True' /></OrderBy><GroupBy Collapse='True'><FieldRef Name='Created' /></GroupBy>";

                        dataColQuery.ViewFields = "<FieldRef Name='Created' /><FieldRef Name='tckStat' />";
                        dataColQuery.ViewFieldsOnly = true;
                        tktData = tktList.GetItems(dataColQuery).GetDataTable();

But I am getting null value everytime the query runs. And I am sure that data exists within the range I am looking to get data.

Please help. Thanks

Answer 1

I got it working after going with an alternative. Follow the below code.

DateTime lstNDaysDate = DateTime.UtcNow.AddDays(-30);
DateTime curntDt = DateTime.Now;

if (oWeb.CurrentUser.RegionalSettings != null)
                        {
                            nedt = Convert.ToDateTime(oWeb.CurrentUser.RegionalSettings.TimeZone.UTCToLocalTime(lstNDaysDate)).Date.ToString("yyyy-MM-dd");
                            curDt = Convert.ToDateTime(oWeb.CurrentUser.RegionalSettings.TimeZone.UTCToLocalTime(curntDt)).Date.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            nedt = Convert.ToDateTime(oWeb.RegionalSettings.TimeZone.UTCToLocalTime(lstNDaysDate)).Date.ToString("yyyy-MM-dd");
                            curDt = Convert.ToDateTime(oWeb.RegionalSettings.TimeZone.UTCToLocalTime(curntDt)).Date.ToString("yyyy-MM-dd");
                        }

SPQuery dataColQuery = new SPQuery();
dataColQuery.Query = "<Where><And><Eq><FieldRef Name='isAct' /><Value Type='Choice'>Yes</Value></Eq><And><Geq><FieldRef Name='Created' /><Value Type='DateTime' >" + nedt + "</Value></Geq><Leq><FieldRef Name='Created' /><Value   Type='DateTime' >" + curDt + "</Value></Leq></And></And></Where>";

And it worked like a charm.