BuyLog In
Ticket Q477778
Visible to All Users

Unable to filter date values in xpo DataSource

created 12 years ago

Hi Devexpress Team,

I am uanable to filter date column records in XPDataView . I am unable to filter today records from XPDataView.

SQL Records:
===========

ReferenceNo      CreatedOn ModifiedOn============ ====================== =======================PUR213001           2013-02-22 17:41:41.480            2013-02-26 13:09:10.863PUR0213002      2013-02-23 12:26:13.353            2013-02-23 14:54:06.450PUR0213004      2013-02-25 11:29:43.527      2013-02-25 11:29:43.527PUR0213005     2013-02-27 14:21:33.287       2013-02-27 17:18:09.980PUR0213006     2013-02-27 17:48:50.810       2013-02-27 17:48:50.810

Here i want to get Today created records through XPDataView Filter by CreatedOn Column

Apply Date Filter:
=============
NameValueCollection multiselectedFilters = new NameValueCollection();

if (Session["MultiCustomFilter"] != null) { multiselectedFilters = (NameValueCollection)Session["MultiCustomFilter"]; } multiselectedFilters.Add("CreatedOn","TODAY");
Session["MultiCustomFilter"] = multiselectedFilters.;
/// <summary> /// Xp DataView Filter /// </summary> /// <param name="datasource"></param> /// <param name="IsXpoCustomFilter"></param> /// <param name="childFKcolumn"></param> /// <returns></returns> public XPDataView ApplyFiltering(XPDataView datasource) { string filterExpression = string.Empty; if (HttpContext.Current.Session["MultiCustomFilter"] != null) { NameValueCollection selectedFilters = (NameValueCollection)HttpContext.Current.Session["MultiCustomFilter"]; foreach (var key in selectedFilters.Keys) { if (key.ToString() != "My Data") { string[] filterVals = selectedFilters.GetValues(key.ToString()).Distinct<string>().ToArray<string>(); string customFilterexps = string.Empty; foreach (string filterVal in filterVals) { string customFilterexp = GetCreatedDate(filterVal, key.ToString()); if (customFilterexps.Length > 0) { customFilterexps += " or " + customFilterexp; } else { customFilterexps = customFilterexp; } }
if (filterExpression.Length > 0) { filterExpression += " and " + customFilterexps; } else { filterExpression = customFilterexps; } }
} filterExpression = string.IsNullOrEmpty(filterExpression) ? datasource.FilterString : datasource.FilterString + " and " + filterExpression; } if (datasource != null && !string.IsNullOrEmpty(filterExpression)) { datasource.FilterString = filterExpression;
} return datasource;
}

public string GetCreatedDate(string period, string column,string customFilterFrom = null, string CustomFilterTo = null) {
string date = string.Empty; string filterexp = string.Empty; switch (period.ToUpper()) {
case "CUSTOM": date = string.Format("[{1}] >=#{0}# and [{1}] <=#{2}# ", Convert.ToDateTime(customFilterFrom.Replace("/", "-")), column, Convert.ToDateTime(CustomFilterTo.Replace("/", "-"))); break; case "TODAY": DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); date = string.Format("[{1}] = #{0}#", dt, column);//.ToString("MM/dd/yyyy") break; case "LASTDAY": date = string.Format("[{1}]=#{0}#", DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy"), column); break; case "THISYEAR": date = string.Format("[{1}] >=#{0}#", new DateTime(DateTime.Now.Year, 4, 1).ToString("MM/dd/yyyy"), column); break;
case "THISMONTH": date = string.Format("[{1}] >=#{0}#", new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToString("MM/dd/yyyy"), column); break; case "LASTMONTH": filterexp ="[{2}] >=#{0}# And [{2}] <=#{1}#"; date = string.Format(filterexp, new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1).ToString("MM/dd/yyyy"), new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1).AddDays(-1).ToString("MM/dd/yyyy"), column); break; case "THISWEEK": int days = (DateTime.Now.DayOfWeek - DayOfWeek.Sunday) - 1; DateTime thisweek = DateTime.Now.AddDays(-(days)); filterexp ="[{2}] >=#{0}# and [{2}] <=#{1}#"; date = string.Format(filterexp, thisweek.ToString("MM/dd/yyyy"), thisweek.AddDays(days).ToString("MM/dd/yyyy"), column); break; case "LASTWEEK": int diff = (DateTime.Now.DayOfWeek - DayOfWeek.Sunday) + 6; DateTime mondayOfLastWeek = DateTime.Now.AddDays(-diff); filterexp = "[{2}] >=#{0}# and [{2}] <=#{1}#"; date = string.Format(filterexp, mondayOfLastWeek.ToString("MM/dd/yyyy"), mondayOfLastWeek.AddDays(diff).ToString("MM/dd/yyyy"), column); break; default: date = string.Format("[{0}] = '{1}'", column, period); break;
} return date; }

Please let me know if anything wrong in my code.

Sign in to comment on this post

Answers approved by DevExpress Support

created 12 years ago (modified 12 years ago)

It seems that your TODAY filter does not work because because you are comparing the DateTime.Now with the column value using the Equals operator, but your values contain different values in the time part. Use the following operator instead:

C#
case "TODAY":  
    DateTime dt = DateTime.Today;  
    return new BetweenOperator(column, dt, dt.AddDays(1)).ToString();

In addition, I recommend that you construct filter expressions using CriteriaOperator instances, and assign criteria to the XPDataView.Filter property. Your approach of creating filter expressions using string manipulations is error-prone and depends on the current culture formatting.

    Sign in to comment on this post

    Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

    Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.