In one of my previous posts, Fun with DATETIME Arithmetics, I introduced a way to use “math” to manipulate
datetime values for effectively generating, calculating, and displaying intervals (i.e. difference between two
datetime values). These mostly work with the addition and subtraction operators (+, -).
In one of the paragraphs, I mentioned multiplication and division, and posed the question about why anyone would ever need to do this.
Any plans for DATEMULTIPLY and DATEDIVIDE?
Multiplication (*) and division (/) won’t work:
SET @d2 = @d1 * 2.0
Msg 257, Level 16, State 3, Line 21
Implicit conversion from data type datetime to numeric is not allowed. Use the CONVERT function to run this query.
But it’s possible if you convert the datetime values to
float first (although I’m not entirely sure why you would ever need to do something like this):
SET @d2 = CONVERT(float, @d1) * 2.0 SELECT @d2 -- result: 1900-01-04 08:30:31.800 SELECT CONVERT(datetime, CONVERT(float, @d1) * CONVERT(float, @d2)) -- result: 1900-01-06 15:02:05.420
Well, it just so happens that I have recently stumbled upon a use-case which could in fact be easily solved using multiplication and division on
Tell me if this looks familiar
Have you ever heard of the use case where one would have need to “round” a
datetime value down to a certain date-part, such as a day, month, hour, minute, etc.?
For example, assuming that we have a datetime value such as
'2021-06-12 12:34:56.456', and we want to “round” it down to:
nearest month -> '2021-06-01 00:00:00.000' nearest day -> '2021-06-12 00:00:00.000' nearest hour -> '2021-06-12 12:00:00.000' nearest minute -> '2021-06-12 12:45:00.000' nearest second -> '2021-06-12 12:45:56.000'
You get the idea, right?
If you wanna do that, there’s an old-timey method to do so using a combination of DATEADD and DATEDIFF functions. For example, here is how to round a
datetime value down to the nearest day:
DATEADD(dd, DATEDIFF(dd, 0, MyDateTimeValue), 0)
The general “algorithm” is this:
- Calculate how many intervals of a specific date-part (e.g. days) are between your
datetimevalue, and the “zeroth” datetime (i.e. “0”):
DATEDIFF(dd, 0, MyDateTimeValue)
- Add that number of intervals to the “zeroth” datetime:
DATEADD(dd, ..., 0)
- Any part of your
datetimevalue which is smaller than the specified date-part will be ignored / truncated.
Simply replace the “dd” date-part with any other date-part as needed, and it would do the job well.
But when is it not good enough?
The method above is very useful and easy to implement… As long as you need to round your
datetime value to something that exists as a supported date-part in the DATEADD and DATEDIFF functions.
But… What if you have an unusual use-case, where you need the rounding to be down to something which does NOT exist as a built-in date-part?
For example, what if you need your
datetime value to be rounded down to the nearest 10 minutes? Or 5 minutes? Or 30 minutes? Or 3 days? There is no date-part for that, buddy.
Time to Multiply your Divisions
But what we DO have is (as you probably have guessed) datetime arithmetics!
Assuming that we can represent our desired interval as a
datetime value (for example, 10 minutes would be:
'00:10:00'), we can plug it into the same “algorithm” that we implemented using the aforementioned DATEADD+DATEDIFF method, but this time make it work with a datetime-typed interval.
It would look something like this:
CONVERT(datetime, FLOOR(CONVERT(float, @MyDateTimeValue) / CONVERT(float, @interval)) * CONVERT(float, @interval) )
The “algorithm” is essentially the same:
- Calculate how many intervals are between your datetime value and the “zeroth” datetime, which would be the same as:
FLOOR(CONVERT(float, @MyDateTimeValue) / CONVERT(float, @interval))
- Add that number of intervals to the “zeroth” datetime, which is the same as:
CONVERT(datetime, ... * CONVERT(float, @interval) )
In other words:
- We convert both the
datetimevalue and the desired
float. This would already represent the difference in days between the given
datetimeand the “zeroth”
- We find how many times the desired interval “fits” into the
datetimevalue (using division).
- We activate the FLOOR function on that result so that anything “smaller” than the desired interval would be ignored / truncated. Now our result is basically something like “how many 10 minutes are in my datetime”.
- Take that result and multiply it by our desired
interval, to get our new
datetimevalue. But wait, it’s actually still
- Convert the result back to
Here is an example script:
DECLARE @MyDateTimeValue DATETIME = '2021-06-12 12:37:54', @interval DATETIME = '00:10:00' SELECT CONVERT(datetime, FLOOR(CONVERT(float, @MyDateTimeValue) / CONVERT(float, @interval)) * CONVERT(float, @interval) )
You can play around with the variables
@interval and see that this method works well.
Additionally, by simply replacing the FLOOR function here with its opposite function CEILING, you can very easily round your
datetime value UP instead of DOWN:
DECLARE @MyDateTimeValue DATETIME = '2021-06-12 12:37:54', @interval DATETIME = '00:10:00' SELECT CONVERT(datetime, CEILING(CONVERT(float, @MyDateTimeValue) / CONVERT(float, @interval)) * CONVERT(float, @interval) )
Play around with this too for a bit to see how this works.
Obviously, using the “vanilla” functions DATEADD and DATEDIFF would be more readable and preferable where possible.
But once in a while, you would come across an unusual requirement that would call for more “creative” solutions.
For these unusual circumstances, I hope you’d find this post here useful 😊.
Pingback: Unusual Rounding via DATETIME Math – Curated SQL