none
TimeSpan data type in DataTable.Select RRS feed

  • Question

  • Hello,

    I need to filter a data table based on TimeSpan values in a column

    Is there any one who could help me on the syntax to use ?

    I have tried the following:

    ColName<='10:00:00' 

    =>Error : Cannot perform '<=' operation on System.TimeSpan and System.String

    ColName<=Convert('10:00:00',System.TimeSpan)

    =>Error : The String '10:00:00' is not a valid TimeSpan value

    The following works about the syntax but gives wrong results

    Convert(ColName,System.String)<='10:00:00'

    So, what is the right expression ?

     

    • Moved by VMazurModerator Monday, December 20, 2010 11:47 AM (From:ADO.NET Managed Providers)
    Monday, July 31, 2006 10:45 AM

Answers

  • Well bhv,

    I spent some time last night trying to get the code to work and using the convert function and the information here:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

    Dim sFIlter as String = "TS >convert(1000. 'System.TimeSpan')" and it errored out with:

     "Cannot perform '=' operation on System.Timespan and System.Timespan

    Taking a look at the timespan classs it has methods for comparisons but does dont have the use of the operators

    I don't believe you will be able to filter by a timespan column using the select method

     

    Tuesday, August 1, 2006 12:59 PM
  • Hi DMan

    Thanks for having spent time on this subject.

    In fact I also spent some time on the subject and the problem comes from several reasons due to limitations (bugs) of the internal expression parser

    Since the expression is provided as a string, the parser tries to convert it into the DataColumn data type and for this it uses the Convert.ChangeType method which requires that the type implements the IConvertible interface and ...

    TimeSpan does not !.

    As you noticed even though you explicitely converts the string into a TimeSpan it gives an error (BUG)

    From that point I though that it should work if I evaluated the string representation of the TimeSpan as follow:

    Convert(ColName,SysTem.String) >= '10:00:00'

    Unfortunatelly and  again a BUG : the Convert statement does not convert the TimeSpan to its string representation ('10:00:00') but into an xml representation (??!!): 'PT10H' (may be an internal "trick" for specific SQL server requirements !!??).

    The problem is that this xml representation is not comparable as a string since it is not 'linear'. in other words:

    the xml representation of '06:00:00' <= '10:00:00' which is 'PT6H' <= 'PT10H' returns (obviously) False

    So to solve my problem I had to create a clone of the TimeSpan structure which implements the IConvertible interface and it works with it. The problem is it is no more native TimeSpan objects!!

    MS strikes back!

    The architects should never forget: "Separation of genders and separation of responsabilities" are master thoughts.

     Derogating to this will soon or later make you fall into tricky situations.

     

    Thursday, August 3, 2006 7:48 AM

All replies

  • Hi!

    I'm assuming that ColName is variable of type TimeSpan. Now with following statement you can do the comparison which will return bool.

    ColName >= TimeSpan.Parse("10:00:00"); // C# code

     ColName >= TimeSpan.Parse("10:00:00") ' VB code

    cheers

    --Sohail

    Monday, July 31, 2006 1:07 PM
  • Hi Sohail

    Thanks for your trying.

    Unfortunatelly your proposal does not match my requirement.

    I may not have been clear enough in my post but the concerned expression is for filtering rows of a DataTable

    Therefore 'ColName' is not a variable but the name of a column of the table and a more detailed code would therefore be:

    Dim MyFilterExpression As String= " ColName <= '10:00:00' "

    Dim Rows() As DataRow = MyTable.Select( MyFilterExpression )

    Therefore your suggestion generates the error : The expression contains undefined function call TimeSpan.Parse().

    Any other idea ?

    best regards

    bhv

     

    Monday, July 31, 2006 2:40 PM
  • DIm TS as TimeSpan = TimeSpan.Parse("10:00:00") ' VB code

    Dim MyFilterExpression = " ColName <=" & TS.ToString

     

    Monday, July 31, 2006 3:00 PM
  • Hi DMan1

    Your suggestion does not work

    DIm TS as TimeSpan = TimeSpan.Parse("10:00:00") ' VB code

    Dim MyFilterExpression = " ColName <=" & TS.ToString

    Gives in fact the same result as:

    Dim MyFilterExpression = " ColName <= 10:00:00 "

    Which does not work since the syntax is not accepted.

    By doing:

    Dim Rows() As DataRow = MyTable.Select ( MyFilterExpression )

    It returns the error: "Cannot interpret token ':' at position15."

     

    Monday, July 31, 2006 5:20 PM
  • Try either putting the timespan in apostropees '10:00:00' or you can try #10:00:00# ...the later (#) is used for expressing dates...Have not tried with the timespan type though
    Monday, July 31, 2006 5:35 PM
  • None of them works

    The first proposal : '10:00:00' => Error : Cannot perform '<=' operation on System.TimeSpan and System.String

    as stated in the first post

    The second proposal : #10:00:00# => Error : Cannot perform '<=' operation on System.TimeSpan and System.DateTime

    No luck! :-)

     

    Monday, July 31, 2006 8:49 PM
  • Well bhv,

    I spent some time last night trying to get the code to work and using the convert function and the information here:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

    Dim sFIlter as String = "TS >convert(1000. 'System.TimeSpan')" and it errored out with:

     "Cannot perform '=' operation on System.Timespan and System.Timespan

    Taking a look at the timespan classs it has methods for comparisons but does dont have the use of the operators

    I don't believe you will be able to filter by a timespan column using the select method

     

    Tuesday, August 1, 2006 12:59 PM
  • Hi DMan

    Thanks for having spent time on this subject.

    In fact I also spent some time on the subject and the problem comes from several reasons due to limitations (bugs) of the internal expression parser

    Since the expression is provided as a string, the parser tries to convert it into the DataColumn data type and for this it uses the Convert.ChangeType method which requires that the type implements the IConvertible interface and ...

    TimeSpan does not !.

    As you noticed even though you explicitely converts the string into a TimeSpan it gives an error (BUG)

    From that point I though that it should work if I evaluated the string representation of the TimeSpan as follow:

    Convert(ColName,SysTem.String) >= '10:00:00'

    Unfortunatelly and  again a BUG : the Convert statement does not convert the TimeSpan to its string representation ('10:00:00') but into an xml representation (??!!): 'PT10H' (may be an internal "trick" for specific SQL server requirements !!??).

    The problem is that this xml representation is not comparable as a string since it is not 'linear'. in other words:

    the xml representation of '06:00:00' <= '10:00:00' which is 'PT6H' <= 'PT10H' returns (obviously) False

    So to solve my problem I had to create a clone of the TimeSpan structure which implements the IConvertible interface and it works with it. The problem is it is no more native TimeSpan objects!!

    MS strikes back!

    The architects should never forget: "Separation of genders and separation of responsabilities" are master thoughts.

     Derogating to this will soon or later make you fall into tricky situations.

     

    Thursday, August 3, 2006 7:48 AM
  • Please use the product feedback center to post this information. 

    http://connect.microsoft.com/Main/content/content.aspx?ContentID=2220

     

    If you post a link to the report I'll vote

    Thursday, August 3, 2006 10:12 PM
  • Thank you for submitting the bug report bhv...I have voted

     

    To all other forum participants please take the time to place your vote:

    https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=176649

    Saturday, August 5, 2006 4:24 PM
  • I also have this problem and can't find a solution.

    I have voted to

    Wednesday, December 15, 2010 12:30 PM