locked
Query help RRS feed

  • Question

  • User-373571878 posted

    Hello, I have these values coming back from a query:

    TotHours....TH_Inminutes...HHMM..........LoggedHrs..........LH_InMinutes............Difference

    36.00 -----  2160-----------  36:00--------  00:31:10 --------1870 -------------- 4.830000

    36.00 * 60 gives me 2160 minutes, 

     31:10 * 60 gives me minutes (31*60 + 10minutes) = 1870

    2160 - 1870 = 290   (290/60)  gives me the 4.83 

    How can I get the 4.83 to look like HH:MM (4:50)   instead of 4.833?

    Side note the HHMM of 36:00 looks to be hours minutes,

    but the 00:31:10 looks like 31 minutes 10 seconds, when it is actually 31 hours and 10 minutes.

    So I am not sure that can be used in a DateDiff function, since they don't look the same and are varchar fields.

    Tuesday, May 1, 2018 8:13 PM

Answers

  • User347430248 posted

    Hi StrangerMike,

    You had asked,"How can I get the 4.83 to look like HH:MM (4:50)   instead of 4.833?"

    Example:

    declare @time int
    set @time = 290
    
    select cast((@time / 60) as varchar(2)) + ':' + cast((@time % 60) as varchar(2))

    Output:

    Other thing you had mentioned that,"Side note the HHMM of 36:00 looks to be hours minutes, but the 00:31:10 looks like 31 minutes 10 seconds, when it is actually 31 hours and 10 minutes. "

    try to provide sample data and your query.

    we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 2:38 AM

All replies

  • User-1716253493 posted

    Cast the value as datetime then you can do datediff or other datetime manipulation

    CAST(REPLACE('00:'+TotHour,'.',':') as DateTime

    Tuesday, May 1, 2018 11:00 PM
  • User347430248 posted

    Hi StrangerMike,

    You had asked,"How can I get the 4.83 to look like HH:MM (4:50)   instead of 4.833?"

    Example:

    declare @time int
    set @time = 290
    
    select cast((@time / 60) as varchar(2)) + ':' + cast((@time % 60) as varchar(2))

    Output:

    Other thing you had mentioned that,"Side note the HHMM of 36:00 looks to be hours minutes, but the 00:31:10 looks like 31 minutes 10 seconds, when it is actually 31 hours and 10 minutes. "

    try to provide sample data and your query.

    we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 2:38 AM
  • User364663285 posted

    Hi,

    You need to multiply the decimal part with 60, to convert it into number of minutes.

    Wednesday, May 2, 2018 8:12 AM
  • User-373571878 posted

    thanks oned_gk

    I tried this:

    CAST(REPLACE('00:'+ PH.TotHrs,'.',':') as DateTime) as DT,

    but received error:  Error converting data type varchar to numeric.

    Or actual line of code seems to be missing a ')' parens. somewhere.

    Wednesday, May 2, 2018 12:47 PM
  • User-373571878 posted

    thanks Deepak

    that difference field of 4.83 is calculate like this:

    ROUND(((TotHours* 60) - LH_InMinutes) / 60, 2) as Difference

    can I try your suggestion of : cast((@time / 60) as varchar(2)) + ':' + cast((@time % 60) as varchar(2)), using this calculation for Difference

    I tried subbing @Time with this: ROUND(((ph.TotHrs * 60) - mtc.TotMinutes) / 60, 2)

    but received error: Arithmetic overflow error converting numeric to data type varchar.

    Wednesday, May 2, 2018 1:03 PM
  • User-373571878 posted

    thanks wmec,

    How can I multiple just the decimal portion?  I will look around to see if I can find something. 

    4.83 .....83 * 60 = 49.8 rounded would be 50, that could be a possibility too.

    Wednesday, May 2, 2018 1:06 PM
  • User364663285 posted

    Try

    declare
    	@t decimal(13,4)
    	set @t=4.83
    	
    select cast(round((@t-floor(@t))*60,0) as int)
    

    to get how many minutes there are, of decimal part!

    Wednesday, May 2, 2018 1:34 PM
  • User-373571878 posted

    Deepak

    I think an issue I am having is decimal vs. int

    cast((((ph.TotHrs * 60) - mtc.TotMinutes) / 60) as varchar(2)) + ':' + cast((((ph.TotHrs * 60) - mtc.TotMinutes) % 60) as varchar(2)),

    Arithmetic overflow error converting numeric to data type varchar.

    seems it does not like ph.tothrs because it is a numeric(6,2) field.   I've tried so many options I am getting lost in them!

    in other words this will run:

    select cast((((36 * 60) - 1870) / 60) as varchar(2)) + ':' + cast((((36 * 60) - 1870) % 60) as varchar(2))

    This will not

    select cast((((36.00 * 60) - 1870) / 60) as varchar(2)) + ':' + cast((((36.00 * 60) - 1870) % 60) as varchar(2))

    Wednesday, May 2, 2018 1:51 PM
  • User-373571878 posted

    ahh, but this worked:

    cast(((CAST((PH.TotHrs * 60) AS INT) - mtc.TotMinutes) / 60) as varchar(2)) + ':' + cast(((CAST((PH.TotHrs * 60) AS INT) - mtc.TotMinutes) % 60) as varchar(2))

    Thank you

    Wednesday, May 2, 2018 2:03 PM