Calculation Between Two Dates To Determine Days, Hours, Minutes And Seconds

Document created by Paul Wyatt - Avison Young on Mar 22, 2018Last modified by ggallo on Mar 29, 2018
Version 2Show Document
  • View in full screen mode

About the Solution/Project: 

About the solution

I wanted to calculate the days, hours, minutes and seconds between two date time stamps, one being the current date and time, and I wanted it to be simple and dynamic.  Uses for this process could be to accurately record the time taken to run a daily dataload - my original purpose - or to calculate the time to or from another date time event, say a meeting or 'go live' calendar event.


In the example below, a dataview is created that displays two figures the first is the numeric value for 13 June 2016 09:00hrs and the second is the numeric value of the current datetime.  The following columns display the number of days, hours minutes and seconds between the two dates.  Selecting the button, refreshes the SQL datareader and updates the current datetime and the number of days, hours, minutes and seconds between then and the start date.


Solution/Project info


The method I'm presenting here is to allow the reader to create and test this method in its entirety.  In this way, the reader will have a full understanding of all the component parts of the method and how they relate of each other.  In the reader's own application, parts of the method, such as how the date is captured or how many cube objects are needed, will differ to meet one's own specific needs as there will be many other ways to apply the concepts of what is shown here.


This method requires the creation of the following objects:



  • Name:       [datevalue]
  • Member:  dtg



  • Name:   Date1            Date2
  • Entity:   datevalue      datevalue
  • Type:   Double           Double



  • Type:   SQL
  • NAME: DAT > Date Test


  • Code:   Datevalue       
  • Cube:   Date1 - [Replace]
  • Cube:   Date2 - [Replace]


- Connect and attach any SQL table.  The table is irrelevant as the process uses the manual editor and a custom SQL script, independent of any tables. 

- Switch to manual box and enter or paste the following SQL script




('dtg',42534.37527,convert(float, getdate())))


NOTES: The values are datevalues, the first is an arbitrary startdate of 13 June 2016 09:00:00 and the second is the value of the current (dynamic) datetime.


Run the datareader to populate the cubes.


CPSX procedure x 1


Create the following steps within a cpsx (capsule) Procedure

NAME: TIME Refresh



  1. SQL Datareader   DAT > Date Test
  2. Refresh Screen


SCREEN - Configuration and Operation

  • Create a screen and place a dataview into it.
  • Configure the dataview layout as follows:
BlockContentTypeFormulaFormatExample ValuesExample Value interpretation
a.Date1CubeNumeric42534.37527Start DTG 13/06/2016 09:00:00
b.Date2CubeNumeric43181.86583Current DTG 22/03/2018 20:46:48
cResultAlgorithmb-aNumeric647.490568sum of b-a
d.DaysAlgorithmint(c)Numeric647no of days
e.HoursAlgorithmmod(c,1)*24Numeric12no of hours
f.MinutesAlgorithmmod(e,1)*60Numeric46no of minutes
g.SecondsAlgorithmmod(f,1)*60Numeric25no of seconds



Insert a button object and attach the cpsx procedure: [Time Refresh] to it.


On selecting the button, the values for each time segment will update to display the time distance from the start date of 13 Jun 2016 at 09:00:00 and the current time.


Attachments/Print Screens/Videos

Attached is an image of a formatted application which uses this method.  I appreciate that I could have done all calcs in SQL and stored the product in a cube for simplicity but I wanted to manipulate and control the component parts of a datetime for future projects, hence this project.




Takeaways/ Hints for other community Members:

The concept I've proposed introduces one method of calculating a difference between times and dates.  There are many other ways one could insert the start date and current date and I would like to learn what methods others would use to pass these initial values. 


I would be interested to learn of any simply method that would allow a user to type in a date which is then converted to its numeric value and passed through the process.  I would also be very interested to learn how other readers might segment the date and time parts.


The example presented lends itself to simplification and concatenation of time parts.  My aim in this solution was to expose each part of the process to the reader.


Creating values within the SQL data reader is my own best practice approach to inserting custom data into a board model.  By this way, I do not have to use txt files and ascii data readers and so the model is more easily portable and, with the use of the daily datareader, entities can be cleared and rebuilt easily.  This method also allows for quicker identification of issues later down the line.


When I have time, I will post a capsule and video of the entire process to my BOARD page.

2 people found this helpful