CA 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?
Attached to this document 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 CA Datacom SQL query. There are several steps involved in using this:
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 CA Datacom SQL, and take advantage of Microsoft Excel functions where possible. Two of the functions used - EOMONTH and EDATE - are part of the Analysis ToolPak add-in, which must be installed and loaded into Microsoft Excel 2003 and 2007, and are part of the base package for 2010 and above.
For more information about loading the Analysis ToolPak add-in, please visit the following links for your version of Microsoft Excel:
2010 and above: The date functions are already included
As always, please contact CA Technologies support for CA Datacom if you have further questions.