How to Round a Time in T-SQL

by

Try this function

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float)
RETURNS datetime
AS
BEGIN
   DECLARE @RoundedTime smalldatetime
   DECLARE @Multiplier float

   SET @Multiplier= 24.0/@RoundTo

   SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar,@Time,121) AS datetime) AS float) * @Multiplier,0)/@Multiplier

   RETURN @RoundedTime
END



select dbo.roundtime('13:15',0.5)

The 1st param is the time to be rounded and the 2nd will be base on your list (0.5-half hour, 1-one hour, …)

More Related Contents:

  1. How to calculate age in T-SQL with years, months, and days
  2. Levenshtein distance in T-SQL
  3. Convert multiple rows into one with comma as separator [duplicate]
  4. How to get sp_executesql result into a variable?
  5. How can I use optional parameters in a T-SQL stored procedure?
  6. Truncate (not round) decimal places in SQL Server
  7. Select query to remove non-numeric characters
  8. T-SQL Decimal Division Accuracy
  9. Selecting a Record With MAX Value
  10. How to convert Seconds to HH:MM:SS using T-SQL
  11. T-SQL: Selecting rows to delete via joins
  12. Combining INSERT INTO and WITH/CTE
  13. T-SQL Pivot? Possibility of creating table columns from row values
  14. Getting the minimum of two values in SQL
  15. TSQL divide by zero encountered despite no columns containing 0
  16. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  17. t-sql get all dates between 2 dates [duplicate]
  18. How to pivot table with T-SQL?
  19. Using Aliases in Where Clause or an Alternative Option?
  20. Testing for inequality in T-SQL
  21. Can I use the MERGE statement in SQL Server 2005?
  22. Replace a newline in TSQL
  23. T-SQL – function with default parameters
  24. How to use an Alias in a Calculation for Another Field
  25. Check if a varchar is a number (T-SQL)
  26. Using a cursor with dynamic SQL in a stored procedure
  27. T-SQL Between Dates Confusion
  28. String.Format like functionality in T-SQL?
  29. How to parse string into date?
  30. Stored procedure hangs seemingly without explanation

Leave a Comment