none
Select in DataTable with condition on DateTime column

    Question

  • Hello. I'm having a problem migrating to .NET 2.0 and it seems like I'm stuck. :(

    The problem is that DataTable.Select() run with condition on a DateTime column doesn't find any rows if the DateTime objects were defined with non-zero time (i.e. not 00:00:00).

    Code Sample:


    System.Data.DataTable table = new System.Data.DataTable();

    table.Columns.Add("MyColumn", typeof(System.DateTime));

    DateTime locTimePoint = System.DateTime.Now;

    for (int i = 0; i < 100; i++)

    {

    //Pick some time point to check equality in select

    if (i == 50)

    {

    locTimePoint = System.DateTime.Now.AddDays(i);

    table.Rows.Add(locTimePoint);

    continue;

    }

    table.Rows.Add(System.DateTime.Now.AddDays(i));

    }

    System.Data.DataRow[] rows1 = table.Select("MyColumn=#" + locTimePoint.ToString(DateTimeFormatInfo.InvariantInfo) + "#");

    System.Data.DataRow[] rows2 = table.Select("MyColumn='" + locTimePoint.ToString(DateTimeFormatInfo.InvariantInfo) + "'");

    System.Data.DataRow[] rows3 = table.Select("MyColumn='" + locTimePoint.ToString() + "'");

    System.Data.DataRow[] rows4 = table.Select("MyColumn=#" + locTimePoint.ToString() + "#");

    System.Data.DataRow[] rows5 = table.Select("MyColumn=#" + locTimePoint.ToString(new CultureInfo("en-us")) + "#");

    System.Data.DataRow[] rows6 = table.Select("MyColumn='" + locTimePoint.ToString(new CultureInfo("en-us")) + "'");


    All 6 arrays are empty

    I've tried using ' and  # as delimiter. I've tried local culture, invariant culture and "en-us". If I use Date without time (for example, Now.Date) - the code works (at least 1, 3 and 5). The method Find() also finds a row with time (I need to define a primary key then - not a solution for me). Inequality conditions also seem to work with time, but I need the equality.

    Does anybody have an idea what's going on? Thank you for your help.

    P.S:

    I'm using VS.NET 2005 8.0.50727.42.

    .NET Framework 2.0.50727

    Wednesday, April 19, 2006 2:46 PM

Answers

  • The Select expression you are using uses DateTime.Parse internally.

    DateTime.Parse does not parse fractions of a second, even if you were to include them in your expression. 

    I can't see DateTime.Parse ever being changed for backwards compatibility reasons.  You basically have two solutions.

    1. Truncate the DateTime to an exact second before inserting in your DataTable, e.g.:



    locTimePoint = TruncateToSecond(System.DateTime.Now.AddDays(i));

     

    There are a number of ways you could implement the "TruncateToSecond" helper method.  Here are two - you might like to test to see which is fastest:



    static DateTime TruncateToSecond(DateTime dateTime)
    {
        return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute, dateTime.Second, dateTime.Kind);
    }

    static DateTime TruncateToSecond(DateTime dateTime)
    {
       
    return new DateTime((dateTime.Ticks / TimeSpan.TicksPerSecond) * TimeSpan.TicksPerSecond, dateTime.Kind );
    }

     

    2. Alternatively you could specify a range of times from N to N + 1 second, for example:



    "MyColumn>=#" + locTimePoint.ToString(DateTimeFormatInfo.InvariantInfo) +
    "# AND MyColumn<" + locTimePoint.AddSeconds(1).ToString(DateTimeFormatInfo.InvariantInfo) + "#";

     

    Wednesday, April 26, 2006 5:45 PM

All replies

  • I've no idea what "genious" logic rules in Select() method.

    If I initialize dates not from DateTime.Now, but with explicit initialization of year, month, day, hour, minute, and second - all 8 array contain the found row.

    Changes in code (original version commented out)


    //DateTime locTimePoint = System.DateTime.Now;

    DateTime locTimePoint = new DateTime(2006, 4, 19, 17, 12, 15);

    for (int i = 0; i < 100; i++)

    {

    //Pick some time point to check equality in select

    if (i == 50)

    {

    //locTimePoint = System.DateTime.Now.AddDays(i);

    locTimePoint = locTimePoint.AddDays(i);

    table.Rows.Add(locTimePoint);

    continue;

    }

    //table.Rows.Add(System.DateTime.Now.AddDays(i));

    table.Rows.Add(locTimePoint.AddDays(i));

    }


    And yes, I did checked the Ticks property. The values are equal in locTimePoint variable and row in the datatable. :(


    - locTimePoint {08.06.2006 17:17:36} System.DateTime
    ...
      Ticks 632853838560266623 long
    +  TimeOfDay {17:17:36.0266623} System.TimeSpan



    -  table.Rows[50][0] {08.06.2006 17:17:36} object {System.DateTime}
      Ticks 632853838560266623 long
    +  TimeOfDay {17:17:36.0266623} System.TimeSpan


    Wednesday, April 19, 2006 3:24 PM
  • Guys, can anybody reproduce the bug? Or am I alone in the universe with my problem?

    BTW, my default locale is "de-DE" (seems to irrelevant, because I tried already all possible combinations of CultureInfos).

    Thursday, April 20, 2006 8:49 AM
  • Karen Liu, am I posting in the wrong forum? Or may be the bug is not reproducible/not that relevant?

    If anybody can (or cannot) reproduce the bug, please, give your feedback. If anybody is interested in correction of the bug (given reproducibility), please, vote on

    http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK49093

    Thank you.

    Wednesday, April 26, 2006 1:23 PM
  • It seems I've provided a bad link to the bug description on the msdn product feedback center.

    Here the same once more

    http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=56ccbf20-0a30-4816-9ce0-e77e802d2647

    Wednesday, April 26, 2006 4:31 PM
  • The Select expression you are using uses DateTime.Parse internally.

    DateTime.Parse does not parse fractions of a second, even if you were to include them in your expression. 

    I can't see DateTime.Parse ever being changed for backwards compatibility reasons.  You basically have two solutions.

    1. Truncate the DateTime to an exact second before inserting in your DataTable, e.g.:



    locTimePoint = TruncateToSecond(System.DateTime.Now.AddDays(i));

     

    There are a number of ways you could implement the "TruncateToSecond" helper method.  Here are two - you might like to test to see which is fastest:



    static DateTime TruncateToSecond(DateTime dateTime)
    {
        return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, dateTime.Hour, dateTime.Minute, dateTime.Second, dateTime.Kind);
    }

    static DateTime TruncateToSecond(DateTime dateTime)
    {
       
    return new DateTime((dateTime.Ticks / TimeSpan.TicksPerSecond) * TimeSpan.TicksPerSecond, dateTime.Kind );
    }

     

    2. Alternatively you could specify a range of times from N to N + 1 second, for example:



    "MyColumn>=#" + locTimePoint.ToString(DateTimeFormatInfo.InvariantInfo) +
    "# AND MyColumn<" + locTimePoint.AddSeconds(1).ToString(DateTimeFormatInfo.InvariantInfo) + "#";

     

    Wednesday, April 26, 2006 5:45 PM
  • Thank you, JocularJoe. It seems that you're right. If I initialize any DateTime structure with a not-zero number of second fractions - Select fails.

    Honestly saying, it's such a big WTF... How much are fractions of second different from fractions of a minute? Why did MS developers decide to truncate fractions of a second (not fractions of a minute or fractions of a millisecond)? This "feature" actually means that the DateTime structure is almost not usable - you must always do a wrapper around it in order to get rid of milliseconds (+you're inherently unable to distinguish two time moments in one second).

    My opinion is that the Select method should be changed so that it concerns the milliseconds in the expression (don't change DateTime.Parse() if you don't want to break the existing code - implement a private method, implement an overload for Parse()). I'm afraid that MS will not do that, and the usual answer will be "By Design". Of course, it is "By Design"! The DateTime was not  _designed_ for usage with some time fractions! The methods AddTicks(), AddMilliseconds etc. exist just for fun. They are not intended to be used!

    And I thank you once more for your help.

    Thursday, April 27, 2006 8:56 AM
  • Actually I was wrong, there is a time format with fractions of a second which can be parsed by DateTime.Parse, and hence can be used for a Select expression.

    Try:



    locTimePoint.ToString("yyyy-MM-ddTHH:mm:ss.fffffff", DateTimeFormatInfo.InvariantInfo)

     

    and your Select should work.

    Having said that, I still think you should generally treat DateTime as an imprecise value (like you would a floating-point value) and test for a range of values rather than an exact value.  Or round explicitly as suggested previously.

    The reason for this is that when you save DateTime values you may lose precision because of:

    - System.DateTime may have a different precision from other DateTime data types used in your application (e.g. System.Data.SqlTypes.SqlDateTime only supports accuracy to 3.33 ms).

    - If you store values in a database you will be limited to the precision of the database you are using.

    Thursday, April 27, 2006 1:26 PM
  • JocularJoe,

    I've tried the format that you provided and it works. I guess, this can be accepted as workaround (thanks again).

    "Having said that, I still think you should generally treat DateTime as an imprecise value (like you would a floating-point value) and test for a range of values rather than an exact value. "

    Well, it may be a hardly evitable limitation of a specific environment, but theoretically DateTime is very precise. It is simpy a number of hundreds of nanoseconds after some moment in the past.

    All these "implementation details" as 3.33 ms resolution in SqlDateTime or the fact that Oracle treats NULL and empty string equally greatly complicate the everyday work. It seems sometimes that developers spend more time researching and debugging stuff like this than doing real job. Unfortunately we have the world that we have.

    Thursday, April 27, 2006 2:15 PM
  • > Unfortunately we have the world that we have
    Yes but wouldn't it be boring if everything was simple.

    Thursday, April 27, 2006 4:12 PM
  • Also, you may try using:

    DataTable dt; // The DataTable to filter 
    DateTime datetimevalue; // The DateTime limit  
    ... 
    DataRow drs[] = dt.Select("Date = #" + String.Format("{0:s}",datetimevalue) + "#"); 

    The sortable DateTime format is usable for this task.
    Monday, September 29, 2008 4:20 AM
  • I did exactly what it says above and  get a FormatException:
    "string was not recognized as a valid DateTime".

    What's wrong?

    Monday, September 07, 2009 9:06 AM
  • Thank´s



    wander junior (Brazil)
    Thursday, January 14, 2010 3:38 PM