Function to Round or Truncate DateTime

• Shane Clarke

  SSC-Addicted

  Points: 448

  More actions

  May 24, 2017 at 8:49 am

  #342945

  Comments posted to this topic are about the item Function to Round or Truncate DateTime

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  May 25, 2017 at 7:27 am

  #1944164

  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

  SSChampion

  Points: 11954

  More actions

  June 30, 2017 at 10:42 am

  #1948980

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


• Shane Clarke

  SSC-Addicted

  Points: 448

  More actions

  September 26, 2017 at 5:08 am

  #1960918

  Luis Cazares - Thursday, May 25, 2017 7:27 AM

  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;


  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 🙂  .

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 26, 2017 at 5:37 am

  #1960924

  Shane Clarke - Tuesday, September 26, 2017 5:08 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 🙂  .

  Why would that be an issue?

  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