Round current timestamp 15 minute

• PSB

  SSChampion

  Points: 13195

  More actions

  April 13, 2015 at 9:17 am

  #306966

  Declare @dt datetime

  set @dt = GETDATE()

  SELECT @dt

  I want to round the current timestamp to something like

  From : 2015-04-13 10:10:15.707 to

  2015-04-13 10:00:00.000

  From : 2015-04-13 10:25:36.793 to

  2015-04-13 10:15:00.000

  From : 2015-04-13 10:31:36.793 to

  2015-04-13 10:30:00.000

  Thanks,

  PSB

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 13, 2015 at 9:24 am

  #1790557

  Sample data used: -

  IF OBJECT_ID('tempdb..#testEnvironment') IS NOT NULL

  BEGIN

  DROP TABLE #testEnvironment;

  END;

  SELECT TOP 10

  IDENTITY( INT,1,1 ) AS ID,

  RAND(CHECKSUM(NEWID())) * 30000 /*(Number of days in range)*/

  + CAST('1945' AS DATETIME) /*(Start date, e.g. '1945-01-01 00:00:00*/ AS randomDateTime

  INTO #testEnvironment

  FROM master.dbo.syscolumns sc1

  CROSS JOIN master.dbo.syscolumns sc2

  CROSS JOIN master.dbo.syscolumns sc3;

  Actual answer based on sample data: -

  SELECT ID,

  randomDateTime,

  DATEADD(MINUTE, DATEDIFF(MINUTE, 0, randomDateTime) / 15 * 15, 0) AS rounded15

  FROM #testEnvironment;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• PSB

  SSChampion

  Points: 13195

  More actions

  April 13, 2015 at 9:30 am

  #1790560

  Thanks .