none
RowFilter string for a DateTimeOffset value RRS feed

  • Question

  • Hi,

    I am trying to filter a dataview against a DateTimeOffset variable.

    After very many permutations I have not been able to format a string that I can pass as the RowFilter property. Do anyone have an example of using a filter string with a DateTimeOffset variable? Or suggestions

    for example, I have tired

    DateTimeOffset tempDateTimeOffset = new DateTimeOffset(focusDate, new TimeSpan());

    string dateTimeOffsetTempString = tempDateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffff zzz");

    string tempFilterString = @"(From = " + dateTimeOffsetTempString + ")";

    dvAllContent.RowFilter = tempFilterString;

    Thursday, March 25, 2010 7:31 PM

Answers

  • Miha, thanks.

    You suggested LINQ. I explored this and found that I could use it in a straight forward manner, without the workaround. LINQ codes is below.

    But I am curious...why was a workaround necessary? Is it because dataview rowfilter parse can not recognize the data type DateTimeOffset (it is recognized by both MS SQL and .net 3.5)? Or am I just buggering up the string parsing?

    *******

     

     

    var myRows= (from r in myTable.AsEnumerable()

     

    where r.Field<DateTimeOffset?>("From") >= myDateTimeOffsetVariable 

    select r);

    • Marked as answer by twsGumby Monday, March 29, 2010 11:54 PM
    Monday, March 29, 2010 11:53 PM

All replies

  • Hi,

    I am trying to filter a dataview against a DateTimeOffset variable.

    After very many permutations I have not been able to format a string that I can pass as the RowFilter property. Do anyone have an example of using a filter string with a DateTimeOffset variable? Or suggestions

    for example, I have tired

    DateTimeOffset tempDateTimeOffset = new DateTimeOffset(focusDate, new TimeSpan());

    string dateTimeOffsetTempString = tempDateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffff zzz");

    string tempFilterString = @"(From = " + dateTimeOffsetTempString + ")";

    dvAllContent.RowFilter = tempFilterString;

    Thursday, March 25, 2010 7:37 PM
  • Try

    dvAllContent.RowFilter = "DateColumn >= #dateTimeOffsetTempString#" 

    This assumes that you are loading timezonesoffset in your dataview, if that is not the case then it will not work...

    Thursday, March 25, 2010 8:10 PM
  •  Does not appear to be the answer. still not solved.

    Hi Chirag - I used your suggestion (putting in what I think is the proper concatenation)

    string

     

    dateTimeOffsetTempString = tempDateTimeOffset.ToString("yyyy-MM-dd HH:mm:ss.ffffff zzz");

    dvAllContent.RowFilter =

    "From >= #"+ dateTimeOffsetTempString +"#";

    I get the error:

    System.Data.EvaluateException was unhandled by user code
      Message="Cannot perform '>=' operation on System.DateTimeOffset and System.DateTime."

    It appears to me that that the # (which works with the DateTime struct) causes the DateTimeOffset to interpreted as a DateTime. 
     

    Thursday, March 25, 2010 8:21 PM
  • Here is a workaround. Add an inmemory column. Then do a loop over rows (or use LINQ) and calculate the offeset and a bool out of it (whether it matches your condition or not). After you done populating the column filter and it.
    Miha Markic [MVP C#] http://blog.rthand.com
    Monday, March 29, 2010 6:33 AM
  • Miha, thanks.

    You suggested LINQ. I explored this and found that I could use it in a straight forward manner, without the workaround. LINQ codes is below.

    But I am curious...why was a workaround necessary? Is it because dataview rowfilter parse can not recognize the data type DateTimeOffset (it is recognized by both MS SQL and .net 3.5)? Or am I just buggering up the string parsing?

    *******

     

     

    var myRows= (from r in myTable.AsEnumerable()

     

    where r.Field<DateTimeOffset?>("From") >= myDateTimeOffsetVariable 

    select r);

    • Marked as answer by twsGumby Monday, March 29, 2010 11:54 PM
    Monday, March 29, 2010 11:53 PM
  • Hello,

    You can try using the RowFilter with the Function "DateTimeOffset". your filter should look like

    "From = DateTimeOffset(datetime, hours, minutes)"

    Where datetime corresponds to the a datetime value (in your example "#"+ dateTimeOffsetTempString +"#"), hours and minutes correspond to the DateTimeOffSet timespan. The value of hours must be between the values of -14 and +14. The value of minutes must be between the values of -59 and +59. The value of the hours and minutes must combined to be in the range of -14 to +14 hours, exclusive.

    Please let me know if this helps.

    Thanks,

    Juan


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, May 12, 2010 12:22 AM
    Moderator
  • This is not really a fix. DateTimeOffset is really giving me a hard time. I cannot use it in filter expressions. I have tried CONVERT([Column], DateTime) and it doesn't work. I am at my wits end! I get the error: Invalid type name 'DateTime'.
    Friday, November 20, 2015 7:21 PM
  • I GOT IT! CONVERT(CONVERT([DateTimeOffsetColumn], System.String), System.DateTime) >= #2015-10-21 12:00:00 AM -07:00#  By first converting to string then converting to date it works. Wow. I spend hours on this.
    Friday, November 20, 2015 8:32 PM