title: TRUNC two times divided by 0.20833333333
body: <p>I come from the world of SQL Server, and have been asked to figure out what's going on with a piece of (ancient) Oracle script.</p>
  <blockquote>
  <p><strong>CAVEAT</strong>: I don't have access to the Oracle server, I just have the script that someone emailed me, and our copied-over data on SQL Server.</p>
  <p>I don't have access to the tasks that have been moving the data from Oracle to SQL Server either. I'm pretty much in the dark -  I have the Oracle script, and (most of) the transformed SQL Server tables.</p>
  </blockquote>
  <p>I'm doing okay interpreting most of it with help from Google, but I can't for the life of me figure out what this is doing:</p>
  <pre><code>decode(TRUNC((fin_time-start_time)/0.208333333333333),0,1,1,2)
  </code></pre>
  <p>I think I've interpreted the <code>decode</code> portion correctly equivilent to:</p>
  <pre><code>case TRUNC((fin_time-start_time)/0.208333333333333)
  when 0 then 1
  when 1 then 2
  end
  </code></pre>
  <p>But the TRUNC function is confusing me. <code>endtime</code> and <code>starttime</code> are times with no date part (eg <code>16:00:00</code> or <code>18:15:00</code>). Again, without access to or any experience with Oracle, I don't know if this means there's a TIME datatype, or if it's just a DATETIME with a 'default' datestamp (eg <code>1900-01-01</code>). In either case, the date is either non-existent or irrelevant.</p>
  <p>The documentation would seem to suggest that <code>TRUNC()</code> passed with no format parameter will just strip the time part. But the time part is all that the column contains. So the presumably resulting <code>0</code> being divided by 0.2083... I'm either on entirely the wrong track, or this is <a href="http://thedailywtf.com/" rel="nofollow noreferrer">The Daily WTF</a> worthy (or, I suspect, both).</p>
  <p>Any ideas?</p>
  <hr />
  <p><strong>UPDATE</strong>: I think I've interpreted it correctly now as:</p>
  <pre><code>case floor(cast(dateadd(minute, DATEDIFF(minute, START_TIME, FIN_TIME), 0) as float) / 0.2083333333333)
  when 0 then 1
  when 1 then 2
  end
  </code></pre>
  <p>Which <em>does</em> appear to return expected values, but I think is still a WTF as values other than 0 and 1 are returned, unless I'm interpreting <code>DECODE()</code> behaviour incorrectly. I assume for non-covered cases without a default parameter, it would return <code>NULL</code> (as <code>case</code> does)?</p>