Searching a datetime field for just the time
Another situation I ran across was to search for events that started between 1pm and 4pm, for example. SQL Server does not do this nicely, as far as I can tell. To do this search, you need to leverage the fact that datetimes are basically floats:
SELECT * FROM MyTable
WHERE StartDate - CAST(FLOOR(CAST(StartDate AS float)) AS datetime) > '13:00'
AND StartDate - CAST(FLOOR(CAST(StartDate AS float)) AS datetime) < '16:00'
Having done many projects that center around dates and times, working with these constructs is never easy. Even the .NET runtime doesn't really have great functionality for doing this, and it seems that every product and language deals with them slightly differently. For example, SQL Server deals with fractions of a second with a decimal point: HH:MM:SS.mmm whereas javascript does the same with a colon: HH:MM:SS:mmm This doesn't seem like it would be a problem until you're passing data around as XML, which is basically a string. All of a sudden, one half of your application is emitting strings that the other half can't use.
And this is just dealing with the same time system. Don't even get me started on converting between Gregorian dates and Hebrew dates or trying to compute whether it's daylight savings time in a particular country (or part of a country)! If you want to watch someone's head explode, tell them to write an IsDaylightSavingsTime function that, given a day, time, and country/state, will return true or false.