Check if a date is greater than another

English
, ,
#1

Version: 7.5.7.1 (X86_64) / LibreOffice Community
Build ID: 47eb0cf7efbacdee9b19ae25d6752381ede23126
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: pt-BR (pt_BR.UTF-8); UI: pt-BR
Calc: threaded

I’m writting a macro in LO Base, and in the middle of this macro I need to check if a date is bigger than another date, I tried if date1 > date2, but is this case it only considers the days, so if date1 is 31/12/2021 and date2 is 23/06/2023, date1 will be bigger because, 31 is bigger than 23. Is there a function that can understand that (in this case) date2 is greater than date1?

#2

So you really store dates as “local” coded strings in your database?
.
You can then covert to a comparable version like 2023-11-30 and compare this.
.
One question would be, how numbers from one to nine are written. With 1/2/2023, you have to find separators, with 01/02/2023 you can directly access substrings.

Some ideas, but note LibreOffice includes only V1.8 of HSQLDB, wich doesn’t handle dates well.:

#3

(Not regarding the “base” tag!)

Seems your “dates” are texts (strings). They are obviously written in the mid-endian format only widespread in the USA in a format used in many English speaking countries. (Sorry I didn’t read the examples thoroughly enough. See comment by @Wanderer below.)
The only human-readable date format making date-strings comparable and sortable the alphabetic (lexicographic) way is ISO 8601 extended. As a format code in Calc this is YYYY-MM-DD . You should use this format exclusively.
The only locale I know which uses this rational format by default is English (Canada). But in any locale Calc will “understand” (interpret) it correctly.

#4

??
Using / is not so common here, but sequence is the same here in Germany with 31.12.2021

#8

Snippet recorded with MRI based on embedded HSQL:

Sub Snippet
  oCurrentController = ThisComponent.getCurrentController()
  oDataSource = oCurrentController.DataSource
  oConnection = oDataSource.getConnection("SA", "")
  REM some query returning some dates
  oObj8 = oConnection.prepareCommand("Ages", 1)
  
  oObj9 = oObj8.executeQuery()
  oObj10 = oObj9.next()
  
  REM find column index of birthday column
  i = oObj9.findColumn("BD")
 
  aDate = oObj9.getDate(i)
  sString = oObj9.getString(i)
  print aDate.Year, aDate.Month, aDate.Day, sString
 End Sub

The result of method getDate is a c.s.s.util.Date struct.
Method getString returns an ISO string.

#5

You can convert date to long number, then you can compare them:
If Clng(date1)>Clng(date2)Then
Var=1
Else
Var=2
End if

More or less

#6

I’d say less. If dates are strings. Did you actually try it?

#7

There is a general misconception concerning dates saying dates are numeric data and can be formatted one or another way.
Misconception? Yes:

  • There is the NullDate issue.
  • There is fundamental ambiguity.
  • There is a temptation to export the formatted string (to whatever) though most widespread “date formats” are really bad, often used in a much worse “abbreviated” variant, and extremely bad if using USA standard.

Better conception? Yes.

  • Use textual representation as specified as ISO 8601 extended.
    Exporting to csv-styled files this is the only sufficiently safe way.
  • Or use dedicated structures depending on the specific case.
  • Never do … what mostly is done!!!
  • You don’t need any conversion then. Such dates sort alphabetically, and in Calc you can even calculate with them based on (limited) trust in automatic conversions.

(Time Zones may thwart tis, of course.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.