June 15, 2011 at 3:38 am
Got a silly requirement as part of a larger report.
Basically, I need to round a DATETIME down to a specific interval.
DECLARE @TABLE AS TABLE(thedate DATETIME)
INSERT INTO @TABLE
SELECT '2011-06-13 07:59:12'
UNION ALL SELECT '2011-06-13 07:48:15'
UNION ALL SELECT '2011-06-13 08:00:00'
UNION ALL SELECT '2011-06-13 07:45:01'
UNION ALL SELECT '2011-06-13 08:11:11'
UNION ALL SELECT '2011-06-13 07:48:15'
For example, if the interval was 1 minute then I would be able to do this: -
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE, 0, thedate),0)
FROM @TABLE
I'm struggling with changing this for specific intervals.
So for an interval of 15, with the table I have given above the expected results are: -
2011-06-13 07:59:12 2011-06-13 07:45:00
2011-06-13 07:48:15 2011-06-13 07:45:00
2011-06-13 08:00:00 2011-06-13 08:00:00
2011-06-13 07:45:01 2011-06-13 07:45:00
2011-06-13 08:11:11 2011-06-13 08:00:00
2011-06-13 07:48:15 2011-06-13 07:45:00
Help is always appreciated 🙂
June 15, 2011 at 3:54 am
D'OH!
Was being stupid. The answer is this: -
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, thedate)/15 * 15, 0)
FROM @TABLE
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply