12

Been struggling with this and can't seem to find the right answer, although there are plenty of mentions for converting, but nothing specific is working.

I need to convert a time with data type of float into hours and minutes. So 13.50 as 13.30. The data type as fixed as float in DB so cannot change. DB is SQL Server 2008R2

Have tried:

cast(cast(floor(fdsViewTimesheet.perStandardHours) as    
float(2))+':'+cast(floor(100*(    
fdsViewTimesheet.perStandardHours - floor(fdsViewTimesheet.perStandardHours)))as 
float(2)) as time) AS STANDARD_HOURS

But I get error message "Explicit conversion from data type real to time is not allowed" Have tried as char instead of as float but query hangs.

What am I doing wrong? I just want to convert a float value into hours and minutes. Would be grateful if someone could point me in the right direction.

user1022772
  • 651
  • 3
  • 14
  • 29

4 Answers4

9

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

rcarvalhoxavier
  • 331
  • 5
  • 7
4

This should work for you

DECLARE @f [real]
SET @f = 13.50

SELECT DATEADD(mi, (@f - FLOOR(@f)) * 60, DATEADD(hh, FLOOR(@f), CAST ('00:00:00' AS TIME)))
Matt Whitfield
  • 6,436
  • 3
  • 29
  • 44
  • How would this work when using field name fdsViewTimesheet.perStandardHours? 13.50 was just an example of data in field fdsViewTimesheet.perStandardHours – user1022772 Jul 22 '13 at 14:12
  • Just replace the @f with fdsViewTimesheet.perStandardHours - job done :) – Matt Whitfield Jul 22 '13 at 14:28
  • Is the '13.50' in your SQL an example of format for @f? So would I replace all examples of @f with fdsViewTimesheet.perStandardHours and keep 13.50 in? Thanks – user1022772 Jul 22 '13 at 14:48
  • It would just be `DATEADD(mi, (fdsViewTimesheet.perStandardHours - FLOOR(fdsViewTimesheet.perStandardHours)) * 60, DATEADD(hh, FLOOR(fdsViewTimesheet.perStandardHours), CAST ('00:00:00' AS TIME)))` – Matt Whitfield Jul 22 '13 at 16:24
  • Thanks for this. Have tried but not working for higher values. I have a field of 37.5 and this is being converted to 13.30? Works for lower values. Some of my fields are actual time values, but other are number of hours and minutes. i.e 37.50 for hours worked in a week. – user1022772 Jul 23 '13 at 08:08
  • In that case, you almost certainly don't want to convert it to a `time` datatype - so what exactly are you looking for? – Matt Whitfield Jul 23 '13 at 08:11
  • Just to convert decimal minutes to time minutes, keep hours as they are, and make sure that the conversion is a data type that can be summed, subtracted etc. with similar values within a report. This looked promising, but getting very odd results: REPLACE( FDSVIEWTIMESHEET.PERSTANDARDHOURS, RIGHT ( FDSVIEWTIMESHEET.PERSTANDARDHOURS,1), RIGHT( FDSVIEWTIMESHEET.PERSTANDARDHOURS,1)*60/100) AS STANDARD_HOURS – user1022772 Jul 23 '13 at 08:46
  • 'and make sure that the conversion is a data type that can be summed, subtracted etc.' - you're asking the impossible there. My advice would be to keep it as a float, and convert it to a display time as late as possible (ideally in the UI). – Matt Whitfield Jul 23 '13 at 10:11
3
DECLARE @f FLOAT = 13.5;

SELECT CONVERT(TIME(0), DATEADD(MINUTE, 60*@f, 0));

Or if you just want hh:mm as a string:

SELECT CONVERT(CHAR(5), DATEADD(MINUTE, 60*@f, 0), 108);

Just be careful if you have values >= 24.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

How about you convert to minutes and add to the 00:00 time like so:

DECLARE @c datetime
select @c = dateadd(mi,fdsViewTimesheet.perStandardHours*60,'00:00')

If you wanted to do it in the statement with Time only:

 select CONVERT(TIME,dateadd(mi,fdsViewTimesheet.perStandardHours*60,'00:00')  )

If you have values that are larger than 24 hours, then the standard datetime and time types in sql cannot hold these. They are limited to holding 24 hour ranges. What you would need to do is store the time representation in a string for example like so:

select cast(floor(fdsViewTimesheet.perStandardHours) as varchar(10)) + ':' + cast(FLOOR( (fdsViewTimesheet.perStandardHours - floor(fdsViewTimesheet.perStandardHours))*60)as varchar(2))
Petio Ivanov
  • 305
  • 1
  • 5
  • Convert works great except when I have a field with over 24 hours. 37.5 is worked out as 13.30? – user1022772 Jul 23 '13 at 08:02
  • 1
    You cannot use datetime/time data types to store hours over 24. Please have a look at this article if you would like to learn how to come up with your own formulas : http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server – Petio Ivanov Jul 23 '13 at 13:21