-2

It's possible to round this DateTime to te nearest 10 minute value, using SQL functions?

Eg.:

input: '09-22-2007 15:04:36.850'
output: '09-22-2007 15:00:00.000'

input: '09-22-2007 15:14:36.850'
output: '09-22-2007 15:10:00.000'
..
input: '09-22-2007 15:54:36.850'
output: '09-22-2007 15:50:00.000'

Seconds and nanos not necessary to '00.000' (but it would be great :))

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LuCaZ
  • 27
  • 6
  • Grab the minute and see if the modulus of min divided by 10 is less than half (5), then use date add to subtract the number of minutes to the nearest 10 or add minutes to go to the next 10. – Daniel Gale Aug 07 '18 at 18:13

2 Answers2

2

If this is a string, you can do:

select left(val, 15) + '0:00:00.000')

For a date/time data type, you can do:

select dateadd(minute, 10 * (datediff(minute, 0, val) / 10), 0)

The 0 is an arbitrary start time in this expression.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
DECLARE @dt datetime
DECLARE @min int

SET @dt = '09-22-2007 15:04:36.850'
SELECT @min = datepart(minute, @dt) % 10

SELECT CASE WHEN @min < 5 THEN
DATEADD(minute, -@min, @dt) ELSE DATEADD(minute, 10 - @min, @dt) END

Output: '9/22/2007 15:00:36.850'

Daniel Gale
  • 643
  • 4
  • 13