Technical Article

Function to Round or Truncate DateTime

,

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

To Use Ths function use the following syntax

SELECT [dbo].[fn_TruncateOrRoundDatetime] ( <@dt, datetime,> ,<@Datepart, varchar(10),> ,<@TruncateOrRound, varchar(10),>)

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

Returns:  "2017-05-23 15:00:00.000"

Valid values for @Datepart:

  Seconds = 'seconds', 'ss','s'

  Minutes = 'minute','mi','n'

  Hours = 'hour','hh','h'

  Day = 'day','dd','d'

  Month = 'month',mm','m'

  Year = 'year','yyyy','y'

Valid values for @TruncateOrRound:

"Truncate"

"Round"

More samples:

------ Use in conjunction with other tables / views

SELECT [dbo].[fn_TruncateOrRoundDatetime] (crdate ,'Month' ,'round') MonthCreated, * from sysobjects

------ Round to Closest Second

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

------ Truncate to Seconds

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 Minutes

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 Hours

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

------ Round to Closest Days

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

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

Rate

3.67 (3)

Share

Share

Rate

3.67 (3)