SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Function to Round or Truncate DateTime

By Shane Clarke,

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:



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

Total article views: 450 | Views in the last 30 days: 26
Related Articles

Round real Value withoot truncation

Round Real Value without Truncation


Custom Rounding and Truncation of Numbers in MDX

MDX applies "bankers' rounding" algorithm in its ROUND() function. This article shows you how to avo...



I would like to round int


rounding of decimals

rounding of decimals


Function to Round or Truncate DateTime

Comments posted to this topic are about the item [B]Function to Round or Truncate DateTime [/B] What...