I am confused about TotalSeconds

Good Day,

As always please forgive me for what seems a basic question but I am struggling with understanding how to convert a TotalSeconds double value back into a date.

Basically I am trying to find half the distance between 2 dates and then display the results in text box on screen but also write that date as part of an update statement back into the SQL table. I have been testing with the Listbox example and cannot get the formatting that I want. In the code below what I am trying to get is to display the “diff” variable as a Sql Datetime in the HalfDateTimeLabel. But then I also want to use it in writing to the SQL record for variable “d”

Thanks as always from a complete novice programmer.

Joe

[code] If Me.ListIndex >= 0 Then
NewValueLabel.Text = "Row " + Str(Me.ListIndex) + " selected: " + Me.Cell(Me.ListIndex,1)
Else
SelectedLabel.Text = “Nothing selected”
End If

dim today as new date
dim dropdate as new date
dim diff as Double
Dim d as new date

today.day=1
today.month=1
today.year=2014

OpenDateTimeLabel.text=str(today)

DropDateTimeLabel.text=dropdate.SQLDateTime

diff=(dropdate.totalseconds-today.totalseconds)/2

HalfDateTimeLabel.text=str(diff,"###.#######")

Dim rec As New DatabaseRecord
rec.DateColumn(“EntryDate”) = d

dim db as new ODBCDatabase
db.DataSource=“DSN=xxx=xxx=xxx”

if db.connect then
db.InsertRecord(“CTS_PreWO”, rec)
else
MsgBox"Error writing record to database!"
End If
[/code]

The delta between two dates is not a “date” - its an elapsed amount of time

If you have two dates, call them date1 and date2, the elapsed time between them is abs(date1.totalseconds - date2.totalseconds)
This will be the number of seconds between them

Now you might want to display that delta as “X years, Y Months, X days” but even that will be inexact since years have varying numbers of days as do months

You could display it as “W days, X hours, Y minutes, and Z seconds” pretty accurately (ignoring leap seconds)

but storing the delta you should store as a integer or floating point value NOT a date

Are you trying to find a date that is halfway between the two dates ?

If you want the date that falls between them using your diff, add it to today.totalseconds.

dim midDate as new date
midDate.TotalSeconds = today.TotalSeconds+ diff
msgbox midDate.SQLDate

You are both correct. What I am trying to find is the date halfway between them.

Yes I was really mucking it up - thanks Norman and Tim - that is exactly what I was trying to do is record a new date at the midpoint between the existing dates.

Joe

ah ok

  dim today as new date
  dim dropdate as new date(2014,1,1)
  dim diff as Double
  dim halfDate as new date

  diff=abs(dropdate.totalseconds-today.totalseconds)/2
  
  if dropdate.totalseconds > today.totalseconds then
      halfdate.totalseconds = today.totalseconds + diff
  else
      halfdate.totalseconds = dropdate.totalseconds + diff
  end if

or even easier:

dim halfDate as new Date halfDate.TotalSeconds = (d1.TotalSeconds+d2.TotalSeconds)*0.5