RowComparision with date

[Federico_Giannetti]

Hi group, I’m sorting my table. With the amounts in Euro it works, and now I’m moving on to sorting the dates.
On the ListBox I see the dates in the format dd-mm-yyyy, and I would like to sort them using the RowComparision, so I acquire them with the following code, which however gives me an error, does it depend on the format?

Var n1 as datetime=datetime.FromString(Me.CellTextAt(row1,column))
Var n2 as datetime=datetime.FromString(Me.CellTextAt(row2,column))

The error is: Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD

I have to convert it in the acquisition, right?

[AlbertoD]

You need to read about DateTime.FromString, without locale it expects the string in SQLDate/Datetime format.

You need to pass a locale to convert ‘dd-mm-yyyy’ to DateTime.

[Tim_Parnell]

You could store the original DateTime object in either the RowTag or CellTag to avoid needing to re-create an object from the string value.

[Federico_Giannetti]

I thought it was a date format issue.

[Federico_Giannetti]

Hi Tim,It’s too complicated for me, I don’t understand what you mean.

[AlbertoD]

@Federico_Giannetti

On the ListBox I see the dates in the format dd-mm-yyyy

Can you go back a little and explain how are you adding the dates to the Listbox?
Are you pulling from a Database?
Are they stored on the database as dd-mm-yyyy, yyyy-mm-dd or other format (maybe seconds from 1970)?

[Jürgen_Czischke]

Verwende z.B. folgende Zeilen in RowComparison

if DatumGroesser(me.CellTextAt(row1,column),me.CellTextAt(row2,column)) then
result=1
else
result=-1
End if
return true

Dabei ist

Public Function DatumGroesser(pDatum1 as String, pDatum2 as String) As Boolean
var datum1, datum2 as double
var dasDatum as DateTime

dasDatum=ErzeugeDateTimeAusString(pDatum1)
if dasDatum<>nil then
datum1=dasDatum.SecondsFrom1970
else
datum1 =0
end if

dasDatum=ErzeugeDateTimeAusString(pDatum2)
if dasDatum<>nil then
datum2=dasDatum.SecondsFrom1970
else
datum2 =0
end if

return datum1> datum2
End Function

und letztlich

Public Function ErzeugeDateTimeAusString(pString as String) As DateTime
var tag, monat, jahr as integer
try
tag=pString.NthField(“-”, 1).val
monat=pString.NthField(“-”, 2).val
jahr=pString.NthField(“-”, 3).val
return New DateTime(jahr, monat, tag)
Catch err As InvalidArgumentException
return nil
End Try

End Function

Das kann man vielleicht noch optimieren, aber so geht es jedenfalls.

Gruß von Jürgen

[Emile_Schwarz]

Frederico:

this works fine (here) and was done for you, for the demonstration “it can be done”…

A downloadable project was shared.

SQLite Data Base Currency: I do it

We discussed recently about how to store / restore data from a SQLite data base, especially Currency. Below is a project that just do that. NB: The leftmost button is a debug for the dates stored in Tag of Column(1), as SQLDate. Second note: the use of Locale for the Date AND for the Currency, of course ! [image] What do the project do ? Click in Create Click in Import and choose the associated .txt file CLick in Read to load / display the data from the .sqlite file Click in Tag Debug to…

[MarkusR]

@Jürgen_Czischke

Cell

it is possible to store any object or value in the CellTag beside the visible Cell (which is only a string format).
a date from database should be possibly a datetime object.in xojo, you can store it in this celltag or as example the seconds since 1970, so you have a ready date object or a value to compare with < = >

see DateTime class

SecondsFrom1970
SQLDate           (SQLite String)
SQLDateTime
FromString
ToString

optimal is a xojo DateTime compatible database field type instead of string or seconds.

[Federico_Giannetti]

I wanted to convert my date 02/12/2024 to 2024/12/02 … but it gives me an error. Can I convert the date from European format to American format, or do I have to convert to seconds?

Error is: “Date is not in an accepted format for parsing”

Var n1 As DateTime
Var n2 As DateTime

Var tz As new TimeZone(“America/New_York”)
Var lc As new Locale(“en-US”)

n1 = DateTime.FromString(Me.CellTextAt(row1,column), lc, tz)
n2 = Datetime.FromString(Me.CellTextAt(row2,column), lc, tz)

[TimStreater]

Var  olds, news, bits() as String

olds = "02/12/2024"
bits = s.split ("/")
news = bits(2) + "/" + bits(1) + "/" + bits(0)

(not tested)

[Federico_Giannetti]

news → Parse error: date needs to be in the format of YYYY-MM-DD HH:MM or YYYY-MM-DD

[TimStreater]

@TimStreater

Var  olds, news, bits() as String

olds = "02/12/2024"
bits = s.split ("/")
news = bits(2) + "/" + bits(1) + "/" + bits(0)

You should fix the obvious mistake (I told you it wasn’t tested).

Var  olds, news, bits() as String

olds = "02/12/2024"
bits = olds.split ("/")
news = bits(2) + "/" + bits(1) + "/" + bits(0)

Now tested.

[Federico_Giannetti]

Im solved you should have inserted - instead of /

olds = Me.CellTextAt(row1,column)
bits = olds.split (“/”)
news = bits(2) + “-” + bits(1) + “-” + bits(0)
n1 = DateTime.FromString(news)

Thanks very much TIM !!

[AlbertoD]

@Federico_Giannetti

I wanted to convert my date 02/12/2024 to 2024/12/02 … but it gives me an error.

@Federico_Giannetti

Im solved you should have inserted - instead of /

olds = Me.CellTextAt(row1,column)
bits = olds.split (“/”)
news = bits(2) + “-” + bits(1) + “-” + bits(0)
n1 = DateTime.FromString(news)

Instead of that code, you can use:

Var olds As String
Var n1 As datetime
Var lc As New locale("it-IT")

olds = Me.CellTextAt(row1,column)
n1 = datetime.FromString(olds, lc)

[Federico_Giannetti]

@AlbertoD

Var olds As String
Var n1 As datetime
Var lc As New locale("it-IT")

olds = Me.CellTextAt(row1,column)
n1 = datetime.FromString(olds, lc)

Thanks, this code works well too