Function to Round or Truncate DateTime

create function fn_TruncateOrRoundDatetime (@dt datetime,@Datepart varchar(10),@TruncateOrRound varchar(10))
returns DateTime
as
-- This Function is used to round to Second, Minute, Hour or Day or to Truncate to  Second, Minute, Hour, Day, Month or Year  and return Datetime Value
-- Sample:
----------------------------------------------------------------------------------------
------ Round to Closest Second
--SELECT 'Round to Closest Second',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'second'  ,'round')

------ Truncate to Second
--SELECT 'Truncate to Second',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'second'  ,'truncate')


------ Round to Closest Minute
--SELECT 'Round to Closest Minute',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'minute'  ,'round')

------ Truncate to Minute
--SELECT 'Truncate to Minute',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'minute'  ,'truncate')


------ Round to Closest Hour
--SELECT 'Round to Closest Hour',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'hour'  ,'round')

------ Truncate to Hour
--SELECT 'Truncate to Hour',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'hour'  ,'truncate')


------ Round to Closest Day
--SELECT 'Round to Closest Day',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'day'  ,'round')

------ Truncate to Day
--SELECT 'Truncate to Day',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'day'  ,'truncate')

------ Truncate to Month
--SELECT 'Truncate to Month',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'month'  ,'truncate')

------ Truncate to Year
--SELECT 'Truncate to Year',[dbo].[fn_TruncateOrRoundDatetime] ('2017-05-23 14:53:45.607' ,'year'  ,'truncate
----------------------------------------------------------------------------------------

begin

declare @DateOut datetime
--select @dt,@Datepart,@TruncateOrRound

-- Round to Second 
if @Datepart in ('ss','s','second') and @TruncateOrRound = 'Round'
select @DateOut = DATEADD(ms, 500 - DATEPART(ms, @dt + '00:00:00.500'), @dt)

-- Truncate to Second
if @Datepart in ('ss','s','second') and @TruncateOrRound = 'Truncate'
select @DateOut =  convert(datetime, convert(char(19), @dt, 126))

-- Round to minute 
if @Datepart in ('mi','n','minute') and @TruncateOrRound = 'Round'
select @DateOut = DATEADD(minute, DATEDIFF(minute, 0, DATEADD(second, 30 - DATEPART(second, @dt + '00:00:30.000'), @dt)), 0) 

-- Truncate to minute
if @Datepart in ('mi','n','minute') and @TruncateOrRound = 'Truncate'
select @DateOut =  DATEADD(minute, DATEDIFF(minute, 0, @dt), 0) 

-- Round to hour
if @Datepart in ('hh','h','hour') and @TruncateOrRound = 'Round'
select @DateOut = DATEADD(hour, DATEDIFF(hour, 0, DATEADD(minute, 30 - DATEPART(minute, @dt + '00:30:00.000'), @dt)), 0) 

-- Truncate to hour
if @Datepart in ('hh','h','hour') and @TruncateOrRound = 'Truncate'
select @DateOut = DATEADD(hour, DATEDIFF(hour, 0, @dt), 0)

-- Round to Day
if @Datepart in ('dd','d','day') and @TruncateOrRound = 'Round'
select @DateOut = DATEADD(day, DATEDIFF(day, 0, DATEADD(hour, 12 - DATEPART(hour, @dt + '12:00:00.000'), @dt)), 0)

-- Truncate to Day
if @Datepart in ('dd','d','day') and @TruncateOrRound = 'Truncate'
select @DateOut = DATEADD(Day, DATEDIFF(Day, 0, @dt), 0)
 

-- Truncate to Month
if @Datepart in ('mm','m','month') 
select @DateOut = convert(datetime,convert(varchar(7), @dt,121)+'-01')

-- Truncate to Year
if @Datepart in ('y','yyyy','year')
select @DateOut = convert(datetime,convert(varchar(4), @dt,121)+'-01-01')

--Return Output
return @DateOut 
end