Convert decimal time to hours and minutes

by

You can try:

DECLARE @HOURS decimal(7,4) = 20.5599
SELECT  CAST(CONVERT(VARCHAR,DATEADD(SECOND, @HOURS * 3600, 0),108) AS TIME)

output : 20:33:35

But remember : Type Time in MSSQL only under 24hrs

If you want greater than 24hrs, try:

DECLARE @HOURS decimal(7,4) = 25.5599
SELECT 
RIGHT('0' + CAST (FLOOR(@HOURS) AS VARCHAR), 2) + ':' + 
RIGHT('0' + CAST(FLOOR((((@HOURS * 3600) % 3600) / 60)) AS VARCHAR), 2) + ':' + 
RIGHT('0' + CAST (FLOOR((@HOURS * 3600) % 60) AS VARCHAR), 2)

output : 25:33:35

— Update

Decimal minutes to more than 24hrs

DECLARE @MINUTES decimal(7,4) = 77.9
SELECT
RIGHT('0' + CAST (FLOOR(COALESCE (@MINUTES, 0) / 60) AS VARCHAR (8)), 2) + ':' + 
RIGHT('0' + CAST (FLOOR(COALESCE (@MINUTES, 0) % 60) AS VARCHAR (2)), 2) + ':' + 
RIGHT('0' + CAST (FLOOR((@MINUTES* 60) % 60) AS VARCHAR (2)), 2);

output: 01:17:54

More Related Contents:

  1. Comma separated results in SQL
  2. How to check if a column exists in a SQL Server table
  3. Understanding PIVOT function in T-SQL
  4. Using T-SQL, return nth delimited element from a string
  5. Get first day of week in SQL Server
  6. Using RegEx in SQL Server
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. Confused about UPDLOCK, HOLDLOCK
  9. INSERT INTO vs SELECT INTO
  10. SQL Server 2008 Empty String vs. Space
  11. How do I use the CONCAT function in SQL Server 2008 R2?
  12. How to set a default value for an existing column
  13. Select / Insert version of an Upsert: is there a design pattern for high concurrency?
  14. TSQL: How to convert local time to UTC? (SQL Server 2008)
  15. SQL Server: combining multiple rows into one row
  16. How do I find a stored procedure containing ?
  17. SQL Recursive CTE: Finding objects linked by property
  18. How to convert number of minutes to hh:mm format in TSQL?
  19. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  20. How do I move a table into a schema in T-SQL
  21. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  22. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  23. How to wait for 2 seconds?
  24. What are row, page and table locks? And when they are acquired?
  25. time format in SQL Server
  26. Generate MD5 hash string with T-SQL
  27. What is the significance of 1/1/1753 in SQL Server?
  28. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  29. Insert Update trigger how to determine if insert or update
  30. Do while loop in SQL Server 2008

Leave a Comment