Move datetime to nearest 15 minute


  
  create table test (dt datetime)
insert into test values('2016-12-07 11:22:25.063')
,('2016-12-07 11:02:25.063')
,('2016-12-07 11:55:55.063')
,('2016-12-07 11:32:25.063')
,('2016-12-07 11:52:25.063') 
,('2016-12-07 11:15:00.000') 

Select 
datetimefromparts (Year(dt),Month(dt),day(dt), datepart(hour,dt), (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60 ,0,0)  dt1, 
dt ,
Dateadd(minute, (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt2

,Dateadd(minute, ceiling(datepart(minute,dt) /15.)*15 % 60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt3

,Dateadd(minute
,datepart(minute, convert(smalldatetime,(ceiling(cast(dt as float) * (24/.25)))/(24/.25))) /* rounded minute in 15 minute interval */
,DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) /* beginning of the hour */
AS dt4

from test

   
drop table test
 

 

https://social.msdn.microsoft.com/Forums/en-US/28a41498-1617-4d05-ba0c-b410adf141a3/conversion-error-while-converting-a-time-and-formatting-it?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s