SQL server, Converting Seconds to Minutes, Hours, Days

by

Depending on the output you want:

DECLARE @s INT = 139905;

SELECT                CONVERT(VARCHAR(12), @s /60/60/24) + ' Day(s), ' 
  +                   CONVERT(VARCHAR(12), @s /60/60 % 24) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  @s /60 % 60), 2) 
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2),  @s % 60), 2);

Result:

1 Day(s), 14:51:45

Or:

DECLARE @s INT = 139905;

SELECT 
    CONVERT(VARCHAR(12), @s /60/60/24)   + ' Day(s), ' 
  + CONVERT(VARCHAR(12), @s /60/60 % 24) + ' Hour(s), '
  + CONVERT(VARCHAR(2),  @s /60 % 60)    + ' Minute(s), ' 
  + CONVERT(VARCHAR(2),  @s % 60)        + ' Second(s).';

Result:

1 Day(s), 14 Hour(s), 51 Minute(s), 45 Second(s).

You can replace 60/60/24 with 86400 etc. but I find it better self-documenting if you leave in the /seconds/minutes/hours calculations. And if you are going against a table, just use column_name in place of @s.

More Related Contents:

  1. to select all the employees who have joined most recently under king
  2. Unpivot with column name
  3. How can I delete using INNER JOIN with SQL Server?
  4. Convert varchar into datetime in SQL Server
  5. sql query to return differences between two tables
  6. SQL Joins Vs SQL Subqueries (Performance)?
  7. Check if a row exists, otherwise insert
  8. How to do pagination in SQL Server 2008
  9. formula for computed column based on different table’s column
  10. SQL comments on create table on SQL Server 2008
  11. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  12. How to write UPDATE SQL with Table alias in SQL Server 2008?
  13. SQL Server 2008- Get table constraints
  14. Restrict an SQL Server connection to a specific IP address
  15. How does this CASE expression reach the ELSE clause?
  16. SQL Server: Cannot insert an explicit value into a timestamp column
  17. Execute Dynamic Query with go in sql
  18. is of a type that is invalid for use as a key column in an index
  19. TransactSQL to run another TransactSQL script
  20. Conditional logic in PostDeployment.sql script using SQLCMD
  21. How can I get a list of element names from an XML value in SQL Server
  22. Why are logical reads for windowed aggregate functions so high?
  23. How can I group time by hour or by 10 minutes?
  24. Custom function with check constraint SQL Server 2008
  25. How to rename something in SQL Server that has square brackets in the name?
  26. Comma Separated values with SQL Query
  27. how to separate string into different columns?
  28. How do I find the data directory for a SQL Server instance?
  29. Safest way to get last record ID from a table
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment