Home Forums SQL Server 2005 T-SQL (SS2K5) Convert DateDiff into Hours, Minutes, Seconds, Milliseconds RE: Convert DateDiff into Hours, Minutes, Seconds, Milliseconds

  • If you must produce formatted output directly from SQL rather than in the front end then your approach is reasonable, however, there are some unecessary modulo operations in your expressions, e.g. ((@I%86400000)%3600000)%60000 is equivalent to @I%60000, so your statement can be rewritten as:

    SELECT

    convert(varchar(10), @I/86400000) + ' Days ' +

    convert(varchar(10), (@I%86400000)/3600000) + ' Hours '+

    convert(varchar(10), (@I%3600000)/60000) + ' Mins '+

    convert(varchar(10), (@I%60000)/1000) + ' sec ' +

    convert(varchar(10), @I%1000) + ' ms ' AS [DD:HH:MM:SS:MS]