T-SQL: Round to nearest 15 minute interval

by

I am currently using a dateadd / datediff variant with a zero (0) date for this. No Casting required:

select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)

GETDATE() is whatever your datetime is.

This will work for dates at least up to the year 5500 before the datediff failes because of an overflow. However if you try to use second accuracy, above will fail right away.

Using another fixed date, like ‘2009-01-01’, or Today’s date (warning, more ugly SQL) will fix that. A future date will also work. As long as it has a time part of 00:00:00 you can base another datetime on it.

for example: round to the nearest 30 seconds:

select dateadd(second, round(datediff(second, '2010-01-01', GETDATE()) / 30.0, 0) * 30, '2010-01-01');

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. DateTime2 vs DateTime in SQL Server
  5. SQL query to select dates between two dates
  6. How to combine date from one field with time from another field – MS SQL Server
  7. How can I select the first day of a month in SQL?
  8. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  9. How to convert DateTime to VarChar
  10. Get day of week in SQL Server 2005/2008
  11. SQL Server Convert Varchar to Datetime
  12. How Can I use “Date” Datatype in sql server?
  13. How to determine the number of days in a month in SQL Server?
  14. Calculate business hours between two dates
  15. SQL Server convert string to datetime
  16. How do I query for all dates greater than a certain date in SQL Server?
  17. A way to extract from a DateTime value data without seconds
  18. How can I compare time in SQL Server?
  19. Difference of two date time in sql server
  20. Custom Date/Time formatting in SQL Server
  21. How to compare datetime with only date in SQL Server
  22. Getting Dates between a range of dates
  23. How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  24. What is the internal representation of datetime in sql server?
  25. SQL SERVER DATETIME FORMAT
  26. SQL query to insert datetime in SQL Server
  27. Postgres Case Sensitivity
  28. Check If the string contains accented characters in SQL?
  29. Count based on condition in SQL Server
  30. Cumulative Total in MS Sql server [duplicate]

Leave a Comment