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


Function to Round or Truncate DateTime


Function to Round or Truncate DateTime

Author
Message
Shane Clarke
Shane Clarke
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 118
Comments posted to this topic are about the item Function to Round or Truncate DateTime
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65288 Visits: 20238
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]

GO

INSERT 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;



Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Bill Talada
Bill Talada
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4644 Visits: 2224
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 datetime2

SET @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
END

IF @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, '')
;
*/

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search