asked
hour conversion
I have DAYS AT OPERATION ( DAYS AT OP) and Hours at Operation (HRS AT OP) columns , CASE WHEN datediff(minute, FLS.staged_datetime, getdate()) < 1440 THEN null ELSE round(datediff(minute, FLS.staged_datetime, getdate()) / 1440, 0) END AS 'DAYS AT OP' , case datediff(minute, FLS.staged_datetime, getdate()) / 60 when 0 then '0' else ltrim(str(datediff(minute, FLS.staged_datetime, getdate()) / 60, 15, 0)) end AS 'HRS AT OP' I used them in my order by clause as below order by 'DAYS AT OP' desc , 'HRS AT OP' DESC I also used the HRS AT OP in my where clause as , case datediff(minute, FLS.staged_datetime, getdate()) / 60 when 0 then '0' else ltrim(str(datediff(minute, FLS.staged_datetime, getdate()) / 60, 15, 0)) end AS 'HRS AT OP' > 8 My intention is to filter holds >8 hrs till the days at Op =1 the hrs at op filtered by desc order but after that when the Days at op value is null the hrs at op filter doesn't continue is desc order please refer to screen shot of the results below. it looks like it took 8 as a string and didn`t continue in desc order, can some one please help me to convert it to hrs so that when I filter it I get the results in Desc order even when the DAYS AT OP is null ![alt text][1] [1]: /storage/temp/2991-dat.png
It looks like 'HRS AT OP' is a character data type: ltrim(str(datediff(minute, FLS.staged_datetime, getdate()) / 60, 15, 0)) Every where you use this expression, you need to wrap it in a cast or convert if you want it to be treated as an integer: cast(ltrim(str(datediff(minute, FLS.staged_datetime, getdate()) / 60, 15, 0)) as int) or convert(int, ltrim(str(datediff(minute, FLS.staged_datetime, getdate()) / 60, 15, 0)))
17 People are following this question.
