Function to Round or Truncate DateTime
-
Comments posted to this topic are about the item Function to Round or Truncate DateTime
-
What about creating a function that would run 5 times faster?
CREATE FUNCTION ifn_TruncateOrRoundDatetime(
@Datetime datetime,
@DatePart char(2),
@Truncate bit
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH Dateparts (part, RoundedDatetime)AS(
SELECT 'SS', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'MI', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'HH', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'DD', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'WK', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'MM', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'QQ', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'YY', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'S', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'N', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'H', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'D', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'W', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'M', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Q', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Y', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Second', DATEADD(ss, DATEDIFF( ss, DATENAME(YEAR, GETDATE()), DATEADD( ms, 500*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'Minute', DATEADD(MI, DATEDIFF( MI, DATENAME(YEAR, GETDATE()), DATEADD( s, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'Hour', DATEADD(HH, DATEDIFF( HH, DATENAME(YEAR, GETDATE()), DATEADD( MI, 30*@Truncate, @Datetime)), DATENAME(YEAR, GETDATE()))
UNION ALL
SELECT 'Day', DATEADD(DD, DATEDIFF( DD, 0, DATEADD( HH, 12*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Week', DATEADD(WK, DATEDIFF( WK, 0, DATEADD( DD, 3*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Month', DATEADD(MM, DATEDIFF( MM, 0, DATEADD( DD, 15*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Quarter', DATEADD(QQ, DATEDIFF( QQ, 0, DATEADD( MM, 1*@Truncate, @Datetime)), 0)
UNION ALL
SELECT 'Year', DATEADD(YY, DATEDIFF( YY, 0, DATEADD( MM, 6*@Truncate, @Datetime)), 0)
)
SELECT RoundedDatetime
FROM Dateparts
WHERE part = @DatePart;
Here's the performance test .
DROP TABLE [dbo].[SampleTable]
GO
CREATE TABLE [dbo].[SampleTable](
[RandomDate] [datetime] NULL
) ON [PRIMARY]GOINSERT INTO SampleTable
SELECT TOP 100000 DATEADD( ms, CHECKSUM(NEWID()), GETDATE())
FROM sys.all_columns, sys.all_columns x;
GO
SELECT *
INTO #Args
FROM ( VALUES('ss'),('mi'),('hh'),('dd'),('mm'),('yy'))dateparts(part)
CROSS JOIN ( VALUES('round',0), ('truncate',1)) RoundOrTruncate(chr,n)DECLARE @Datetime datetime, @Sysdatetime datetime2 = SYSDATETIME()
SELECT @Datetime = RandomDate
FROM SampleTable
CROSS JOIN #Args;SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());
SELECT @Sysdatetime = SYSDATETIME();SELECT @Datetime = [dbo].[fn_TruncateOrRoundDatetime] (RandomDate ,part ,chr)
FROM SampleTable
CROSS JOIN #Args;SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());
SELECT @Sysdatetime = SYSDATETIME();SELECT @Datetime = RoundedDatetime
FROM SampleTable
CROSS JOIN #Args
CROSS APPLY [dbo].[ifn_TruncateOrRoundDatetime] (RandomDate ,part ,n);SELECT DATEDIFF(ms, @Sysdatetime, SYSDATETIME());GO
DROP TABLE #Args; -
Interesting, I took a very different approach when I wrote this similar function years ago.
CREATE FUNCTION dbo.DateTime2Precision (@dt datetime2(7), @precision varchar(7))
returns datetime2
AS
-- written BY Bill Talada
BEGIN
DECLARE
@filler varchar(27),
@length int,
@fixed datetime2SET @filler = 'cc00-01-01 00:00:00.0000000'
--'ccyy-mm-dd hh:mm:ss.mmmmmmm'SET @length =
CASE @precision
WHEN 'century' THEN 2
WHEN 'year' THEN 4
WHEN 'month' THEN 7
WHEN 'day' THEN 10
WHEN 'hour' THEN 13
WHEN 'minute' THEN 16
WHEN 'second' THEN 19
ELSE 0
ENDIF @length = 0
SET @fixed = @dt
ELSE
SET @fixed = cast(LEFT(CONVERT(varchar(27), @dt, 121),@length) + RIGHT(@filler,27-@length) AS datetime2)RETURN @fixed
END
go/*
DECLARE @dt datetime2;
SET @dt = SYSDATETIME();
SELECT
dbo.DateTime2Precision(@dt, 'century'),
dbo.DateTime2Precision(@dt, 'year'),
dbo.DateTime2Precision(@dt, 'month'),
dbo.DateTime2Precision(@dt, 'day'),
dbo.DateTime2Precision(@dt, 'hour'),
dbo.DateTime2Precision(@dt, 'minute'),
dbo.DateTime2Precision(@dt, 'second'),
dbo.DateTime2Precision(@dt, '')
;
*/ - Luis Cazares - Thursday, May 25, 2017 7:27 AM
Only issue is when joining with other tables. That's why the original sample used Scalar Function. This would be great if you do as scalar. Thanks for your feedback
. - Shane Clarke - Tuesday, September 26, 2017 5:08 AMOnly issue is when joining with other tables. That's why the original sample used Scalar Function. This would be great if you do as scalar. Thanks for your feedback .
Why would that be an issue?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply