none
Sorting of null values in DateTime columns RRS feed

  • Question

  • I have DateTime columns in my Product DataTable called StartDate and EndDate.

     

    They indicate when the product started to be stocked and ceased to be stocked.

    If StartDate is null that means the product has always been stocked (since the system was introduced).  If EndDate is null that means the product is still stocked.

     

    If I sort my Product DataTable by StartDate ASC I get all the products where StartDate is null before the ones that have a value for StartDate in ascending order of that StartDate.  That is what I want - it shows the oldest products first and the newest products last.

     

    However, if I sort my Product table by EndDate ASC I get all the null EndDate Products followed by those that have an EndDate in ascending date order.  This is not what I want.  I want the most obsolete products listed first finishing with those that are still stocked (ie the null date ones).

     

    How can I affect the handling of null dates in my EndDate column such that they sort as high values not low values?  I have tried setting NullValue to MinValue for StartDate and MaxValue for EndDate but this doesnt seem to affect how NullValues are sorted.

     

    My current workaround is to add SortableStartDate and SortableEndDate columns where null values are replaced by MinValue and MaxValue and sort on them instead but this is expensive and seems unnecessary given that DateTimes are structs and therefore do actually contain values behind the scenes even if they are presented as empty.

     

     

    Wednesday, June 6, 2007 11:53 AM

All replies

  • I think the problem here is that you are using the same value to represent two different things in different contexts.  The treatment of this value is uniform across columns, but you want it to be treated differently given a different context.  Ideally null values should convey no information other than the absence of a value, however, you want it to be treated as a special value or flag.  You can use default values for these columns (e.g. MinValue for the start column and MaxValue for the end column) and from now on those values will be set when no value is given.  You would then have to update all your current null values to have the new default value.  Any other sorting solution will likely require significant computational overhead per column in order for you to enforce this context specific value treatment.

     

    Hope this helps,

     

    John (MSFT)

    Wednesday, June 6, 2007 9:28 PM
  • I agree that I am trying to overload the concept of null to mean two different things in different contexts.

    I also agree that it would be better in the data layer to set MinValue and MaxValue as the defaults rather than using nulls.  However, in the user interface I don't want to present MinValue and MaxValue I want to present an empty field, which is why I was taking that approach.

     

    If I was to switch to using MinValue and MaxValue in the Data Access Layer I will need to write code in the Presentation Layer that will convert MinValue and MaxValue to null string on display and vice versa for insert/update.  I don't particularly want to write RowDataBound event handlers etc for every GridView that contains dates.  Is there a way I can write the mapping code once and reuse it across my web pages efficiently?  Or is this now off-topic for this forum?

     

    jks

    Thursday, June 7, 2007 9:16 AM
  • Hello jks, I am very interested in hearing how you sorted the DataTable.

    I have tried for hours to get the DataTable sorted, but have not succeeded yet. Could you please post the code you used.
    Thanks,

    Anders...
    Tuesday, September 16, 2008 9:32 AM
  •  

    Hellos,

    I have a problem like that ...

     

    I have to sort people who hand up first. according this it must be ASC. I set datetime info when first hand up.

     

    something like that ..

     

    dtCurrentUsers = new DataTable("theCurrentUsers");

    dtCurrentUsers.Columns.Add(new DataColumn("UserID", typeof(int)));

    dtCurrentUsers.Columns.Add(new DataColumn("RoleID", typeof(int)));

    dtCurrentUsers.Columns.Add(new DataColumn("Username", typeof(string)));

    dtCurrentUsers.Columns.Add(new DataColumn("FullName", typeof(string)));

    dtCurrentUsers.Columns.Add(new DataColumn("iconUrl", typeof(byte[])));

    dtCurrentUsers.Columns.Add(new DataColumn("statusIcon", typeof(byte[])));

    dtCurrentUsers.Columns.Add(new DataColumn("handReqType", typeof(int)));

    dtCurrentUsers.Columns.Add(new DataColumn("handTime", typeof(DateTime)));

    dtCurrentUsers.Columns.Add(new DataColumn("VoteValue", typeof(char)));

    dtCurrentUsers.Columns.Add(new DataColumn("isTalking", typeof(bool)));

    dtCurrentUsers.Columns.Add(new DataColumn("isDenied", typeof(bool)));

    dtCurrentUsers.Columns.Add(new DataColumn("handOrder", typeof(int)));

    dtCurrentUsers.Columns["handOrder"].DefaultValue = 0;

    dtCurrentUsers.Columns["handTime"].DefaultValue = DateTime.MinValue; // it can be null value. it's not important...

    dtCurrentUsers.Columns["isTalking"].DefaultValue = false;

    dtCurrentUsers.Columns["isDenied"].DefaultValue = false;

    dtCurrentUsers.Columns["handReqType"].DefaultValue = 0;

    dtCurrentUsers.RowChanged += new DataRowChangeEventHandler(dtCurrentUsers_RowChanged);

    dtCurrentUsers.RowDeleted += new DataRowChangeEventHandler(dtCurrentUsers_RowDeleted);

    dtCurrentUsers.TableNewRow += new DataTableNewRowEventHandler(dtCurrentUsers_TableNewRow);

    dgOnlineUsers.ItemsSource = dtCurrentUsers;

     

    When i sort like this,

     

    dtCurrentUsers.DefaultView.Sort = "RoleID ASC, handTime ASC , FullName ASC";

     

    it sorts from null or minvalue to right now. it's normal. but i need to sort by who handup first. so i tried to make another void beforesorting ...

     

    bool bSortingCounting = false;

    private void beforeSorting()

    {

    if (bSortingCounting) return;

    bSortingCounting = true;

    if (dtCurrentUsers == null)

    return;

    dtCurrentUsers.DefaultView.Sort = "handTime DESC";

    int lastUser = dtCurrentUsers.Rows.Count;

    int firstUser = 0;

     

    foreach (DataRow item in dtCurrentUsers.Rows)

    {

    if (Convert.ToDateTime(item["handTime"]) == DateTime.MinValue)

    {

    lastUser++;

    item["handOrder"] = lastUser;

    }

    else

    {

    firstUser++;

    item["handOrder"] = firstUser;

    }

    }

    bSortingCounting = false;

    }

     

    then i set default sort to ;

     

    dtCurrentUsers.DefaultView.Sort = "RoleID ASC, handOrder ASC , FullName ASC";

     

    I hope i have been described my problem. Is there any way to make this? Or where is the error?

     

    Thanks in advance...

     

     

     

     

     

     

    Tuesday, September 16, 2008 7:54 PM
  • Maybe I do not understand propblem correctly, but why do not you want to sort DESC from the beginning, like

     

    "RoleID ASC, handTime DESC , FullName ASC" or "handTime DESC, RoleID ASC, FullName ASC";

     

     

     

    Wednesday, September 17, 2008 9:57 AM
    Moderator
  •  

    Hi,

    thank you for answer. the first hand up must be first and last hand up must be last. first in first out logic.

     

    for example,

     

    nesim send a handup request at 18:23

    aram send a handup request at 18:25

    can send a handup request at 18:22

     

    and there are also 2 people who hasn't hand up. their hantime value is null or datetime's min value.

     

    so i need to sort like this...

     

    can, nesim, aram and the others ...

     

    that's all. Smile

     

     

     

     

     

    Wednesday, September 17, 2008 10:19 AM
  • >Hello jks, I am very interested in hearing how you sorted the DataTable.

    >I have tried for hours to get the DataTable sorted, but have not succeeded yet. Could you please post the code you used.

     

    All I did was to add two additional columns:

    SortableStartDate and SortableEndDate.

     

    SortableStartDate is set to a chosen MinValue if NULL (in the Stored Proc that populates the DataTable) using the T-SQL construct

    ISNULL(StartDate, CAST('1753-01-01' AS datetime)) AS SortableStartDate

     

    SortableEndDate is set to a chosen MaxValue if NULL

    using ISNULL(EndDate, CAST('9999-01-01' AS datetime)) AS SortableEndDate

     

    You can then sort on the Sortable columns and show the columns that contain NULLs to achieve the desired result.

     

     

    Wednesday, September 17, 2008 10:40 AM
  •  

    Hi,

     

    I set null value to max value. Than it sorted as i wanted Smile the key was max malue. thank you jks Smile

    Wednesday, September 17, 2008 11:33 AM