SQL: Using DATEADD with bigints

by

Just do the problematic DATEADD in two steps, starting with a coarser time unit (seconds, minutes, hours etc.), then dropping back to the fine grained one for the remainder.

Avoid going to the level of weeks and months though as that would require actual calendar calculations and we would prefer the system to handle that.

Example below needs to calculate a start time given a (possibly) large current duration in milliseconds.

-- large durations can overflow the integer argument needed for DATEADD

-- so do as two steps subtracting minutes (60000ms) and then remaining milliseconds.

DATEADD(ms, -large_duration_ms%60000, DATEADD(minute, -large_duration_ms/60000, GETDATE()))

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. T-SQL split string
  3. How can I get column names from a table in SQL Server?
  4. How can I select the first day of a month in SQL?
  5. GROUP BY to combine/concat a column [duplicate]
  6. Select statement to find duplicates on certain fields
  7. How to turn IDENTITY_INSERT on and off using SQL Server 2008?
  8. Using a Variable in OPENROWSET Query
  9. How to find current transaction level?
  10. EXISTS vs JOIN and use of EXISTS clause
  11. Check for file exists or not in sql server?
  12. How to cast variables in T-SQL for bulk insert?
  13. How to create a PivotTable in Transact/SQL?
  14. Group DateTime into 5,15,30 and 60 minute intervals
  15. concatenate two database columns into one resultset column
  16. Why is a UDF so much slower than a subquery?
  17. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  18. T-SQL and the WHERE LIKE %Parameter% clause
  19. Custom aggregate function (concat) in SQL Server
  20. UNIX_TIMESTAMP in SQL Server
  21. Which SQL query is faster? Filter on Join criteria or Where clause?
  22. UPDATE and REPLACE part of a string
  23. Improve SQL Server query performance on large tables
  24. Average of multiple columns
  25. Show all rows that have certain columns duplicated
  26. Select SQL Server database size
  27. Check if a parameter is null or empty in a stored procedure
  28. Cannot Resolve Collation Conflict
  29. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data
  30. Display next event date

Leave a Comment