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 ?
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.
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