Here is a small tip for SQL Server that I needed today.  We have a time based report of payments that we make for our clients.  This report is used to double check that all payments have been made and is required to be sorted by the date the payment was created and after that the client’s name.

However, on running the report we were getting weird orders in the query.  I tracked this down to the fact that we’re storing date and time for the date that the payment was created and our ORDER BY clause (ORDER BY DateCreated, Client.Fullname) meant that if the time changed, the ordering would restart for the Client.Fullname field.

So the obvious solution is to do an ORDER BY on the date portion only of the DateCreated field.  Since SQL Server stores the datetime records as 2 4 byte integers, we can do some math magic to strip the time.

The first 4 byte portion holds the days since the 1st of January 1900, with the second portion storing the number of milliseconds since midnight.  This means that we can simply convert the datetime field to a float value, run floor() which will strip the digits after the decimal point and then convert back into datetime.

Which works wonders.  Here is the SQL ORDER BY clause that we are now using in the system:

ORDER BY cast(floor(cast(DateCreated as float)) as datetime), Client.Fullname