Confirming Datacom SQL timestamp results with MS Excel

Products

• Datacom
• Datacom/DB

Issue/Introduction

Datacom SQL provides different ways to perform arithmetic on dates and times, but there is no singular function to provide a difference of two timestamp columns or values. Since there are multiple ways to determine this difference, is there a way to use a Microsoft Excel spreadsheet to validate the calculations found in my SQL query?

Environment

Release: 15.1
Component: Datacom/DB SQL

Resolution

In the attached file, there is a Microsoft Excel spreadsheet called TimestampCalculationTest.xlsx. There is a procedure outlined to use this spreadsheet to validate the duration difference produced in the Datacom SQL query. There are several steps involved in using this:

1. After running the DBSQLPR query to produce a report of the two timestamp fields and the two Elapsed Time fields (if desired), save the output to a file and remove everything except the data records. Alternatively, you might want to enter or cut/paste your entries into this or another spreadsheet. If manually entering into this sheet, adjust the format of the cells if needed and copy the formula from columns H through M to your rows to see the results, otherwise, continue with step 2.
2. Transfer the file to the PC as a .txt file, and then open the file using Microsoft Excel. You may need to specify the date fields in their proper format (YMD, for example) and you may need to drop unneeded fields. When complete, your file should be open in Excel with 6 columns like these below.
3. Once your data is formatted like this example, you can copy the cells H7:M7 to the same columns on your opened spreadsheet. Paste these copied cells to cover the whole range of rows.
4. Copy cells J6:K6 to the top row of your spreadsheet, in the columns where the SQL Match Excel? results are found. These cells will count the number of times the SQL calculation did not match the Microsoft Excel calculation, so you can review them further. To find the mismatched cell(s), find "False" in the values for that column.

This spreadsheet uses formulas to calculate the duration between two dates in the format of number of days, hours, minutes, and seconds and also years, months, days, hours, minutes, and seconds. These formulas are based on the calculation algorithm concepts found in Datacom SQL, and take advantage of Microsoft Excel functions where possible.