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
More »
