db2db2-400db2-luw

Convert Date to Integer or Dec

I would like to join on yyyymm OF a Date and wanna see which has better performance.

ON A.CLM_NB = B.CLM_NB
AND LEFT(DEC(HEX(Date)),4) = LEFT(DEC(HEX(CLM_Date)),4)

or

ON A.CLM_NB = B.CLM_NB
 AND DEC(VARCHAR_FORMAT(Date,"YYYYMM")) = DEC(VARCHAR_FORMAT(Date,"YYYYMM"))
Solution

  • second one is likely to be faster. The first query converts the date values to hexadecimal strings, then converts them back to decimal numbers. The second query converts the date values to strings, then converts them to decimal numbers. Converting strings to decimal numbers is typically faster than converting hexadecimal strings to decimal numbers.

    <table>
  <thead>
    <tr>
      <th>Query</th>
      <th>Execution time (ms)</th>
    </tr>
  </thead>
  <tbody>
    <tr style="background-color: lightgray">
      <td>LEFT(DEC(HEX(Date)),4) = LEFT(DEC(HEX(CLM_Date)),4)</td>
      <td>1000</td>
    </tr>
    <tr style="background-color: white">
      <td>DEC(VARCHAR_FORMAT(Date,"YYYYMM")) = DEC(VARCHAR_FORMAT(Date,"YYYYMM"))</td>
      <td>100</td>
    </tr>
  </tbody>
</table>