SQL Server Performance

Calculating the Hour in Decimal Value

Discussion in 'Contribute Your SQL Server Scripts' started by gangulyarindam, Mar 15, 2005.

  1. gangulyarindam New Member

    Hi,<br /><br />Following SELECT - Command will help in calculating Time difference between START and END time in Decimal :<br /><br />SELECT DISTINCT CONVERT(VARCHAR(30), dbo.Mytable.START,101) As IDLEDATE, dbo.Mytable1.USERID As USERNAME, <br /> dbo.Mytable2.SHIFTCODE As SHFCODE, dbo.Mytable3.PROJCODE As PROJCODE, <br /> CASE <br /> WHEN DATEDIFF(ss, dbo.Mytable.START, dbo.Mytable.END) &gt;= 3600 <br /> THEN CAST(DATEDIFF(ss, dbo.Mytable.START, dbo.Mytable.END)/3600 As Varchar(2)) + '.' <br /> + CASE<br /> WHEN ((DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)%60)*100)/60 &lt;= 10<br /> THEN '0' + CAST(((DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)%60)*100)/60 As Varchar(5))<br /> ELSE<br /> CAST(((DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)%60)*100)/60 As Varchar(5))<br /> END<br /> WHEN DATEDIFF(ss, dbo.Mytable.START, dbo.Mytable.END) &lt; 3600 <br /> AND DATEDIFF(ss, dbo.Mytable.START, dbo.Mytable.END) &gt;= 60<br /> THEN '0.' <br /> + CASE <br /> WHEN (DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)*100)/60 &lt;= 10<br /> THEN '0' + CAST((DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)*100)/60 As Varchar(5)) <br /> ELSE<br /> CAST((DATEDIFF(mi, dbo.Mytable.START, dbo.Mytable.END)*100)/60 As Varchar(5)) <br /> END<br /> ELSE <br /> '00.00'<br /> END AS TOTALHRS,<br /> CONVERT(VARCHAR(30), dbo.Mytable.START,101) As IDLEFROM, <br /> CONVERT(VARCHAR(30), dbo.Mytable.START,10<img src='/community/emoticons/emotion-11.gif' alt='8)' /> As STARTTIME,<br /> CONVERT(VARCHAR(30), dbo.Mytable.END, 101) As IDLETO,<br /> CONVERT(VARCHAR(30), dbo.Mytable.END, 10<img src='/community/emoticons/emotion-11.gif' alt='8)' /> As ENDTIME,<br /> dbo.Mytable.IDLECODE As IDLETYPE,<br /> '' as RECTYPE,<br /> -- for edited<br /> 'FALSE'as EDITED,<br /> dbo.Mytable.EDITEDBY As APPRVBY,<br /> '' as KEYDATE,'' as SERVNAME,'' as [UNIQUE],'' as Duplicate<br />FROM dbo.Mytable <br /> INNER JOIN dbo.Mytable1 ON dbo.Mytable.TRANSID = dbo.Mytable1.TRANSID<br /> INNER JOIN dbo.TASKINOUT ON dbo.Mytable1.USERID = dbo.TASKINOUT.USERID<br /> INNER JOIN dbo.Mytable3 ON LEFT(dbo.TASKINOUT.XFILENAME, 2) = dbo.Mytable3.Mytable3<br /> INNER JOIN dbo.Mytable2 ON dbo.Mytable1.USERID = dbo.Mytable2.USERID <br />WHERE <br />dbo.TASKINOUT.START BETWEEN CONVERT(VARCHAR(30), '02/01/2003 6:00:00 AM', 120) AND CONVERT(VARCHAR(30), '02/08/2004 6:00:00 AM', 120) <br />AND CONVERT(VARCHAR(30), dbo.TASKINOUT.START, 101) = CONVERT(VARCHAR(30), dbo.Mytable.START, 101)<br />/*<br />ORDER BY dbo.Mytable.START<br />*/<br /><br />I will be satisfied if it works out for some one in development.<br /><br />Thanks & Regards<br /><br /><br />Arindam Ganguly

Share This Page