Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

formatting time value to hh:mm:ss

I hope I am posting this to the right location as I am developing a new report.  Anyway, this is coming out of our telephone DB system and one column represented is Duration, which is an int column a...
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Anonymous

     

    Try a DAX as below.

    fmtCol = 
RIGHT ( "0" & INT ( TableName[Duration] / 3600 ), 2 )
    & ":"
    & RIGHT (
        "0"
            & INT ( ( TableName[Duration] - INT (TableName[Duration] / 3600 ) * 3600 ) / 60 ),
        2
    )
    & ":"
    & RIGHT ( "0" & MOD (TableName[Duration], 3600 ), 2 )

    Or deal with the format in query.

    select Duration, convert(varchar(10),DATEADD(second,Duration,0),108) fmtSecs from t1

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Had a question about D:HH:MM:SS, hopefully this helps.

 

Dtime (meas) = 
VAR vDur = <<enter object as seconds>>
    RETURN INT(vDur/86400) & ":" &                       //Days
	RIGHT("0" & INT(MOD(vDur/3600,24)),2) & ":" &    //Hours 
	RIGHT("0" & INT(MOD(vDur/60,60)),2) & ":" &      //Minutes
	RIGHT("0" & INT(MOD(vDur,60)),2)                 //Seconds
  • ydaoud's avatar
    ydaoud
    Frequent Visitor
    8 years ago

    just a click on a translate button and this is so helpful thanks a lot.

     

    PS: use jbocachicasuggested website, if you want to convert from seconds to this format HH:mm:ss, I used the following expression using DAX. I just created a new measure using the existing measure in seconds, because my seconds rae in a measure not a column, well I am getting my Data from Analysis Services.

     

    Measure :=  FORMAT(FactTable[MeasureName in seconds]/86400, "HH:mm:ss") 

    By magic you have it in one single line haha.

     

    Read about Format Function here ==> https://msdn.microsoft.com/en-us/query-bi/dax/format-function-dax