I like what you did with getting the date part of a SQL date/time stamp. But, I need to compare just the time part of two dates. How can I get the only the time part of a date/time stamp?
Comparing times in a SQL date/time stamp has always been a bit hairy. Over time, I have found it much easier to store time outside of a date/time stamp and instead, keep it in a 5 character text field in 24-hour time format (ex. 17:30). This way, I can keep dates and time separate. Of course, that's for more simple dates where I don't need to know seconds - something I might do for an event management system. This has it's benefits but it also has limitations that I won't go into now.
But, that aside, let's say you do have your time in a date/time stamp. Getting the time portion of the date/time stamp is very similar to how we got the date portion. The difference is that this time, we only care about the post-decimal numbers, not the pre-decimal numbers. So, in order to get the time, you have to subtract the "INT" of the date/time from the FLOAT cast of the date time:
CAST( ( -- The float cast will give us the full date -- representation as a number. CAST( h.date_created AS FLOAT ) - -- The floor/float cast will give us just the -- "date" of the date/time stamp. FLOOR( CAST( h.date_created AS FLOAT ) ) ) AS DATETIME )
We can't just cast the date to an INT because that will round the value. This can be confusing because in my experience, SQL does not always round when casting from FLOAT to INT... but going from DATETIME to INT, it does seem to round consistently. So that's why we do the FLOOR() of the FLOAT CAST. That equation will provide us with just the decimal part of the date/time stamp. Then, once cast back to DATETIME, you will have a date/time stamp with a constant date and the real time.
NOTE: Keep in mind that the "date" portion is still there! It's just that now, it's a constant value (whatever the zero date in the database is).
Now, before you go about casting stuff back to DATETIME, which is an extra processing step, think about what you are doing. If you are just comparing dates for greater/lesser quality, then you don't need to cast back to DATETIME since comparing the resultant decimal (from our cast-math) will yield the same outcome. Things such as selecting a record with the MAX() time or something along those lines will handle just fine using the numeric representation.
Needless to say, this is a lot of headache for a simple comparison. That is why I store my times separate when I need to utilize the time in any sort of complex fashion.
Want to use code from this post? Check out the license.