convert time and date formats?

Hi,
I am adding the capability for my users to store Reminder / Alert details in a database and be displayed in a Listbox.

In a Timer I need to check if the current date and time matches the exact info in a row, and if so, display an alert MsgBox.

For example:
Row 1 might contain Hello in the Description (Column 1), contain 04:55 in the Time (column 2), and contain 25-12-2014 (UK Date Format) in the Date (column 3).

I have the code below, which initialises a Date Object, and then loops through the rows of my ListBox.
I am perplexed however, as to how I convert the current time and date into the same format as column 2 and column 3, and then compare them (for each row).

I apologise for not being able to explain it any clearer :frowning:

[code]dim today as new date

for iRow As Integer = Window1.lb_boxes.ListCount - 1 DownTo 0
if ???
end if
next[/code]

I would recommend storing your date/time either as Date.SQLDateTime or as Date.TotalSeconds but if the format is out of your hands then do something like…

[code] DIM today As NEW Date
DIM currentTime As String = today.ShortTime.NthField(" ", 1) // the short time has AM/PM appeneded so lets remove that, does not respect 24 hour setting
if (currentTime.Len = 4) then
currentTime = “0” + currentTime
end if

for iRow As Integer = Window1.lb_boxes.ListCount - 1 DownTo 0
if (currentTime = ROW2) then
end if
next[/code]

dont :stuck_out_tongue:
the display of the data can be in whatever localized format you want (using short date) - but thats JUST for display
compare using a fixed format (sqldatetime or something)

basically IF you attach the “date” that the reminder is for to the cell tag in the listbox then you can show whatever you want but the date object in the tag you can use in a fixed format

otherwise you’re going to contort yourself badly trying to match something like 10-11-2014 (is it the 10 of Nov or the 11 of oct ?)

Norman
Thanks, I understand what you said - (my dates will ALWAYS be in the UK format).

Shao
The first part of your code made perfect sense and does exactly what I need.

I am however, having trouble understanding your next part of code:

if (currentTime = ROW2) then

Do I need to replace ROW2 with something else?

Should it read:

if (currentTime = (iRow,22)) then

or:

if currentTime = (iRow,2) then

Hi,
Ok, after guidance from the members on here I have come up with a possible solution.

I am tying to convert the current time into the following format: 05:45 and the current date into the following format: 25-12-2014

It needs to be foolproof (hence the longwinded code).
Could someone please let me know if my code below will achieve this.

[code]//Create a new Date object
DIM today As NEW Date

DIM currentHour As String = today.Hour
//convert to double digits
if (currentHour.Len = 1) then
currentHour = “0” + currentHour
end if

DIM currentMinutes As String = today.Minutes
//convert to double digits
if (currentMinutes.Len = 1) then
currentMinutes = “0” + currentMinutes
end if

DIM combinedFullTime As String = today.Hour + “:” + today.Minutes

DIM currentDate As String = today.Day
//convert to double digits
if (currentDate.Len = 1) then
currentDate = “0” + currentDate
end if

DIM currentMonth As String = today.Month
//convert to double digits
if (currentMonth.Len = 1) then
currentMonth = “0” + currentMonth
end if

DIM currentYear As String = today.Year

Dim combinedFullDate As String = currentDate + “-” + currentMonth + “-” + currentYear[/code]

If you are getting your date from a DB it will be in SQLDateTime formate e.g. 2013-12-25 05:45:00. Just store this in a celltag as Norman suggested & compare your Today.sqldatetime to that. You are over complicating things otherwise, and will run into problems later if someone changes their shortdate/shorttime settings even in UK time format.

Wayne
Thank you very much for the help.

However - when my user originally selects the date, they will choose the day, month and year from 3 separate PopupMenus - the 3 choices are then added together (with a hyphen inbetween) and then saved as a TEXT string in the database.

That is why I am now converting the current date and current time into the same formats.

I understand it is probably overcomplicating things a bit - but I am also trying to learn from the experience.
I therefore, would like to know if my code above would work :slight_smile:

Richard
Are you prepared to learn from my experience?

  1. Never expect a user to enter data correctly - they won’t.
  2. Always validate data & tell the user they’ve F###up because they will.

Dates should always be stored in a db as a date object - never as text - relying on the users date/time format settings is a sure way to crash your app. SQLDateTime is the only format you want to store date/time data. Especially in a db.

In short as Norman said DON’T - just don’t.

The best way to avoid user errors is to not let them make those errors

Ok - this is driving me nuts.
How on earth can the second DIM statement in the code below be fine BUT the third DIM causes a “this item does not exist” error???

[code]DIM today As NEW Date

DIM currentHour as Variant = today.Hour
//convert to double digits
if (currentHour.Len = 1) then
currentHour = “0” + currentHour
end if

DIM currentMinutes as Variant = today.Minutes
//convert to double digits
if (currentMinutes.Len = 1) then
currentMinutes = “0” + currentMinutes
end if[/code]

Also, the following statement throws a type mismatch error “Expected Integer but got string”?

 DIM combinedFullTime as Variant = today.Hour + ":" + today.Minutes

Don’t get nuts yet. Read carefully. Today.minute instead of today.minutes as you did above in your third dim for currentMinutes which explains the non existing item.

You forgot to convert integer to string, and it is again minute, not minutes. Here, corrected :

DIM combinedFullTime as Variant = str(today.Hour) + ":" +str( today.Minute)

This will do:

Dim today As New Date() Dim currentHour As String = Format(today.Hour, "00") Dim currentMinute As String = Format(today.Minute, "00")
Never us a Variant if somehow possible. Using currentHour as Integer and String is bad practice, since you loose type safety.

Wow - thanks guys, those last 2 posts sorted the problem out!

One last question: Anyone have any ideas as to why my LAST block of code fails?
I am trying to loop through my listBox and see if any rows have data that matches the current time and date.

[code]Dim today As NEW Date

Dim currentHour As String = Format(today.Hour, “00”)
Dim currentMinute As String = Format(today.Minute, “00”)
DIM combinedFullTime as String = currentHour + “:” + currentMinute

Dim currentDate as String = Format(today.Day, “00”)
Dim currentMonth as String = Format(today.Month, “00”)
Dim currentYear as String = Format(today.Year, “0000”)
Dim combinedFullDate as String = currentDate + “-” + currentMonth + “-” + currentYear

// THIS BLOCK OF CODE FAILS
for iRow As Integer = Window1.lb_boxes.ListCount - 1 DownTo 0
if combinedFullTime = (iRow,2) and combinedFullDate = (iRow,3) Then
MsgBox(“Test”)
end if
next[/code]

What should that be?

... = (iRow,2)

The first steps in LISP programming? Better try:

... = Cell(iRow,2)

… to get the cell value.

Eli - I’m a bit lost now :frowning:

An example of one of my ListBox Rows might contain the string 05:45 in column2 and 25-12-2014 in column3.
I am therefore, trying to loop through all rows of my ListBox and check to see if Column 2 AND Column 3 of each row, corresponds to combinedFullTime and combinedFullDate respectively.

Hope that made more sense.

Again: What should that be?

if combinedFullTime = (iRow,2) ...

This is not valid Xojo code. I assumed you wanted to get the value of the cell at row iRow and column 2, so use

if combinedFullTime = Cell(iRow,2)

Eli:
I have now adapted my code as follows - and I get an error stating that the item Cell does not exist?

for iRow As Integer = Window1.lb_boxes.ListCount - 1 DownTo 0 if combinedFullTime =Cell(iRow,2) and combinedFullDate= Cell(iRow,3) Then MsgBox("Test") end if next

Put Window1.lb_boxes. in front of it.

That did the Trick.

Thank you so much for your patience !