CODEWITHSUNDEEP
sqlsql-server-2008
ElMilado
ElMilado

Reputation: 13

SQL Server Round a DateTime to the nearest 5 seconds?

Just wondering if anyone can help me with this. I would like to round the SQL datetime to the nearest 5 seconds

For example the dates on the left should give me the following result on the right.

I have seen a similar solution for minute intervals and I have tried to apply it to seconds but it doesn't give the ideal result. 

declare @DT datetime

set @DT = convert(datetime,'14:08:20')
select dateadd(s,(datepart(s,dateadd(s,1,@DT))/5)*5,
    dateadd(mi,datediff(mi,0,dateadd(s,1,@DT)),0))

However, 1900-01-01 14:08:23.000 gives me 1900-01-01 14:08:20.000, which is not correct.

Any help is appreciated.

Upvotes: 1

Views: 3992

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

With some usage of DATEPART() and ROUND(), you can achieve this

Query

declare @t table (dt datetime);

insert @t 
select
'1900-01-01 14:08:20.000' union all select
'1900-01-01 14:08:21.000' union all select
'1900-01-01 14:08:22.000' union all select
'1900-01-01 14:08:23.000' union all select
'1900-01-01 14:08:24.000' union all select
'1900-01-01 14:08:25.000';

select dt, 
       dateadd(second, round(datepart(second,dt)*2,-1) / 2-datepart(second,dt), dt)
from   @t
order by dt;

Results:

dt                      rounded
----------------------- -----------------------
1900-01-01 14:08:20.000 1900-01-01 14:08:20.000
1900-01-01 14:08:21.000 1900-01-01 14:08:20.000
1900-01-01 14:08:22.000 1900-01-01 14:08:20.000
1900-01-01 14:08:23.000 1900-01-01 14:08:25.000
1900-01-01 14:08:24.000 1900-01-01 14:08:25.000
1900-01-01 14:08:25.000 1900-01-01 14:08:25.000

Upvotes: 2

Related Questions