Sorting a time value in a dataset

Question

In my current solution, I am converting a DateTime value, "Time" in my database using CONVERT so it displays in a ##:##AM/PM format, and I realize that CONVERT just takes whatever datatype and turns it into a VarChar, or a String once its in C#. This is making my sort work incorrectly in my Gridview. I am sorting the columns in my DataSet returned from my stored procedure. However it isnt sorting by AM/PM since its a string literal, and not a DateTime.

What is the best way to sort Time values? Should I use a different datatype, like TIME in my database? Different CONVERT command? I'm stumped! I can't use a 24 hour format, that's the only restriction. Thanks!

Answer 1

A couple of thoughts:

• Could you use AM 01:23 / PM 01:23 format - this would sort well.
• If you can return the times from the database in DATETIME format, but with the date set to some 'constant date' (e.g. 2000-01-01 hh:mm), you could almost certainly put a format string on the GridView column to display just the time, whilst still enabling sorting by the underlying value.

Answer 2

I use ticks: http://www.codeproject.com/KB/database/DateTimeToTicks.aspx Or (if possible) DateTime - and handle it in my grid.

You can add an additional column with the date/converted date which is hidden in you gui but used to sort.