How to order by time and time only in C#??

Question

Hi everyone!! I have this recorded in SQL Server:

1- startTime (datetime): 5/2/2009 08:30 (brazilian time format: d/m/y)

2- startTime (datetime): 4/2/2009 14:30 (brazilian time format: d/m/y)

My application just records time... the date it's SQL that generates by itself be getting the date of today.

When I ask VS 2008 to order this datetime fields, it returns me that code #2 is before #1, because 4/2/2009 comes before 5/2/2009.

But, actually I want it to order by time only and ignore the date.

Is it possible??

Thanks!! André

Answer 1

from d in dates
     orderby d.TimeOfDay
     select d;

or

dates.OrderBy(d => d.TimeOfDay);

---

Note: This will work as long as this is plain LINQ and not LINQ-to-SQL. If you're using LINQ-to-SQL to query your database, you'll need something that will translate to SQL.

Answer 2

Try this in your sql:

ORDER BY startTime - CAST(FLOOR(CAST(startTime AS Float)) AS DateTime)

Answer 3

You might also try fixing your app so it always saves the same base date with the time (like '01/01/1900' or whatever) and then you do not have to do all these slow and inefficient date stripping operations every time you need to do a query.

Or as Joel said, truncate or strip off the date portion before you do the insert or update.

Answer 4

Hi everyone! I'm using Linq-To-SQL and I don't want the date. Just the time... :-) The date is coming along for the ride. Thanks!!

Answer 5

@DJ: How can I save the same date (like '01/01/1900') to my DB using C#?

Thanks!!

Answer 6

I used to work on a Time & Attendance system where we stored the time as an int field with just the number of minutes in there. Since we only needed to record down to the nearest minute that was fine for us.

So, I would suggest have an int field with enough capacity to store whatever granularity of the day you need.

Minutes in a Day = 1440 (int16)
Seconds in a Day = 86,400 (int32)

I'm sure you can work out the rest!

Answer 7

Well, you can't really store a datetime without a date, but you could just store the total seconds as an double (using @florian's method).

You'd have to add a second method to convert this back to a date in your object, if you still need a date, such as:

public class BusinessObjectWithDate
{
    private string _someOtherDbField = "";
    private double _timeInMS = 0; // save this to the database

    // sort by this? in sql or in code.  You don't really need this 
    // property, since TimeWithDate actually saves the _timeInMS field
    public double TimeInMS {      
        get { return _timeInMS; }
    }
    public DateTime TimeWithDate { // sort by this too, if you want
        get { return (new DateTime(1900,1,1).AddMilliseconds(_timeInMS)); }
        set { _timeInMS = value.TimeOfDay.TotalMilliseconds; }
    }
}

var f = new BusinessObjectWithDate();
MessageBox.Show( f.TimeWithDate.ToString() );  // 1/1/1900 12:00:00 AM
f.TimeWithDate = DateTime.Now;
MessageBox.Show( f.TimeWithDate.ToString() );  // 1/1/1900 1:14:57 PM

You could also just store the real date time, but always overwrite with 1/1/1900 when the value gets set. This would also work in sql

public class BusinessObjectWithDate
{
    private DateTime _msStoredInDate;
    public DateTime TimeWithDate
    {
        get { return _msStoredInDate; }
        set {
            var ms = value.TimeOfDay.TotalMilliseconds;
            _msStoredInDate = (new DateTime(1900, 1, 1).AddMilliseconds(ms));
        }
    }
}