Time difference between 2 date and time values

rajeshbagaria

Forums

In a database, there are 4 columns (2 date values i.e. start date and end date and 2 time values i.e. start time and end time). In other dates, start is represented in 2 separate columns (date and time) and end is represented in 2 separate columns (date and time). I want to find the difference between start and end (in number of days upto 2 decimal places) considering both date and time. What would be the equation?
Set AddedField = NewTable.NewField
AddedField.Name = "ChangedOnDateFA"
AddedField.Type = WI_DATE_FIELD
AddedField.Length = 10
NewTable.AppendField AddedField
(Another field for time)
Set AddedField = NewTable.NewField
AddedField.Name = "ChangedOnDateZOIC"
AddedField.Type = WI_DATE_FIELD
AddedField.Length = 10
NewTable.AppendField AddedField
(Another field for time)
Set AddedField = NewTable.NewField
AddedField.Name = "TimeTakenZOIC"
AddedField.Type = WI_VIRT_NUM
AddedField.Decimals = 2
AddedField.Equation = "@Age(ChangedOnDateZOIC, ChangedOnDateMKTG)"
NewTable.AppendField AddedField
With regards,
Rajesh Bagaria

Brian Element Thu, 01/21/2021 - 18:15

You can use the following field equation:

AddedField.Equation = "@AgeDateTime( DATE , TIME , DATE1 , TIME1 )"

The date fields have to be in a date format and the time fields have to be in the time format.

rajeshbagaria Fri, 01/22/2021 - 09:22

Thanks
Can equations / calculated values be used in variables inside IDEAScript before saving them to database like TimeTakenMktg = @DToDays(ChangedOnDateMKTG) + @Tton(ChangedTimeMKTG)/86400 - @DToDays(ChangedOnDateL0) - @Tton(ChangedTimeL0)/86400
recOutput.SetCharValue "TimeTakenMktg", TimeTakenMKtg
There is a specific intention and circumstances where equation may not be directly usable in fields.

Brian Element Fri, 01/22/2021 - 09:58

You don't actually have to use the SetCharValue, it is much faster and more efficient to just create the virtual or numeric field and paste the equation into the equation editor.  You usually only want to use the SetCharValue or the equavalent if you can't build it directly in the equation editor as it should be always faster using the equation editor.

Also certain equation editor functions are not available in IDEAScript and I know the @Tton is one of them, so you would have to build the equivalent in VB.

rajeshbagaria Fri, 01/22/2021 - 11:18

Hi Brian,
Thanks for your quick response. On an unrelated topic, can I find out whether in the loop where i am reading records from a database, one by one, whether the end of database has reached.
My database consists of workflow cycles (one row for each approval stage) and all workflows may not be have the same number of levels. I am processing these workflow cycle i.e. one for loop for one cycle. Thus I want to break the loop after reaching the end of database.
Thanks again for the quick support.

Brian Element Fri, 01/22/2021 - 14:42

If I am going through all the records I usually prefer the rs.getAt() function to loop through all the records.  The basic code is this:


Sub Main
	Dim db As database
	Dim rs As RecordSet
	Dim rec As Record
	Dim count As Long
	Dim i As Long
	' Open the database.
	Set db = Client.OpenDatabase("Sample-Detailed Sales.IMD")
		count = db.count
		
		' Obtain the RecordSet from the database.
		Set rs = db.RecordSet
	
			For i = 1 To count
				rs.GetAt(i)
				Set rec = rs.ActiveRecord
				'do something with each record
			Next i
			Set rec = Nothing
		Set rs = Nothing

	Set rec = Nothing

End Sub