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:

"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

Total article views: 491 | Views in the last 30 days: 25
 
Related Articles
FORUM

Round real Value withoot truncation

Round Real Value without Truncation

ARTICLE

Custom Rounding and Truncation of Numbers in MDX

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

FORUM

Round

I would like to round int

FORUM

rounding of decimals

rounding of decimals

FORUM

Function to Round or Truncate DateTime

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

Tags
 
Contribute