Is there a function in the API that converts DateTime values from the format used for the Locale to ISO 8601 (or a roughly compliant format)?
I guess: it MUST be uno-API, any other language( 
) with its own »datetime-api« doesnt fit your needs?!
also not Basic:
format( date, "yyyy-mm-dd\THH:MM:SS.000")
Nice. But how do I fill the structure of type com.sun.star.util.DateTime having a date-time string in the locale format without analyzing it with my own code…
Yes. I can do this in Basic (and in a similat way with a Calc function. But how would I get the compound and nulldate dependent (what I deprecate) value for the variable date of your example.
It’s not just “my needs”. I would expect that LibO comes with a built-in tool for such an often needed conversion.
Basic may be an integrated tool of LibO, but it’s also just “another language” in the sense of what I mean here.
This doesn’t depend on any personal preferences. My point is that
- A language coming with its own “engines” (collation for sorting, RegEx) can cause confusion / incompatibilities.
- A software like LibO supporting an API at all should come with tools for all the often needed tasks.
The Format function is implemented using the built-in formatter, used everywhere else, and exposed in API as NumberFormatter service. There is no need for any dedicated “format as ISO date” function, when we have a general-purpose formatter.
(Note that some minor deviations of Basic’s Format are caused by legacy, are handled separately, and are irrelevant to this specific discussion)
Complete roundtrip ??
Sub Example_roundtrip
msgbox cdatefromUnodatetime(CDateToUnoDateTime(cdate("2025-08-22 12:24:18")))
End Sub
you may now throw »format« on it, but that gives the same result as
msgbox format(cdate("2025-08-22 12:24:18"), "yyyy-mm-dd\THH:MM:SS.000")I don’t understand this, given that every date system used in the whole history of humanity was (and is) nulldate-based? CE is based on a nulldate. Julian day is itself a nulldate. Time since Big Bang is based in nulldate. Show me any system not based on that?
I obviously didn’t spell my problem sufficiently. Let me explain a specific example:
(I’m on Win 10 running LibO 25.8.0.4.)
For example now about the modification DTs of local files.
Even silly Win shows them roughly ISO8601 conforming (using a space in place of the “T”). That’s OK.
As my locale setting I generally use en-cd (getting a reasonable “locale” DT handling).
But now I get as a DT designator the String “25.08.2024 17:03:45” as it is default for de-de and want to convert it to “2024-08-25 17:03:45”.
Same thing with different locales and including the slashed formats (4-digit-year needed, of course, but probably allowing for the outdated AM/PM TOD).
Based on my own experiences and “research” I needed to know the stubborn locale standards, and to anylyze the DT string with my code.
Due to my mental shortcomings I couldn’t yet get a solution based on the replies above.
Function malformattedDatetimeConvertedToISOlike(Optional pAsIs As String, _
Optional pSupposedLocale As String) As String
Const outFormat = "YYYY-MM-DD HH:MM:SS"
If IsMissing(pAsIs) Then pAsIs = "25.08.2024 17:03:45"
If IsMissing(pSupposedeLocale) Then pSupposedLocale ="de-de"
' ...
' ...
'out = ...
malformattedDatetimeConvertedToISOlike = out
End Function
Format(CDate(pAsIs), "YYYY-MM-DD HH:MM:SS")
CDate is designed to take anything “convertible to date” according to the current locale. If you need to use a specific locale, use NumberFormatter API mentioned above. It has detectNumberFormat and convertStringToNumber methods.
There may be a difference in the usage of words. “nulldate-dependent” is used by me as depening on an arbitrary nulldate for the conversion to a numeric representation of DT values.
This wouldn’t be too bad if there were a global and any-software understanding about the date which should be used. Actually the situation is bad as you know.
The Big Bang has no date as of now, and I wouldn’t call 1582-10-15 a nulldate, but you may see it this way. Regarding its historical origin, however, it’s at least a bit less arbitrary than “the first day of the rule of Ramses III”, and all the different nulldates used by existing software are actually defined based on the Gregorian calendar. Do you know a better way?
Yes, that’s at the heart of my problem. If I write some code, I want to get it working independent of the locale, and of arbitrary settings in somebodys user-profile.
I will study the NumberFormatter now more attentive to understand it better (if possible for me).
Note that if your “pSupposedLocale” is a BCP 47 language tag (your “de-de” isn’t, strictly speaking), then use a com.sun.star.lang.Locale with empty Country, "qlt" in Language, and pSupposedLocale in Variant, when preparing the locale for use with NumberFormatter.
Yes, but the language “en” is common to locales with (at least) 3 different default formats for dates. I don’t know BCP 47 notation regarding this. And some en-locales may even use the comma as the decimal separator.
I meant, the proper language tag would be de-DE. But maybe our machinery would also accept de-de using some relaxed processing.
You’re right, of course.
sorry cannot resist:
from datetime import datetime as dt, UTC
stamp = "20|22/08;15,33,17" ## yeah this is a rather crazy date-string"
# but we can convert to »utc« isoformatted in one strike!
out = dt.strptime( stamp, '%y|%d/%m;%H,%M,%S').astimezone(UTC).isoformat()
print(out)
# gives:
2020-08-22T13:33:17+00:00
Function malformattedDatetimeConvertedToISOlike(Optional pAsIs As String, _
Optional pSupposedLocale As String) As String
Const outFormat = "YYYY-MM-DD HH:MM:SS"
If IsMissing(pAsIs) Then pAsIs = "25.08.2024 17:03:45"
If IsMissing(pSupposedLocale) Then pSupposedLocale ="de-de"
locale = new com.sun.star.lang.Locale
locale.Language = "qlt"
locale.Variant = pSupposedLocale
supplier = com.sun.star.util.NumberFormatsSupplier.createWithLocale(locale)
formatter = CreateUnoService("com.sun.star.util.NumberFormatter")
formatter.attachNumberFormatsSupplier(supplier)
detected = formatter.detectNumberFormat(0, pAsIs)
d = formatter.convertStringToNumber(detected, pAsIs)
malformattedDatetimeConvertedToISOlike = Format(d, outFormat)
End Function
With that,
msgbox malformattedDatetimeConvertedToISOlike("08.09.2024 17:03:45", "en-us")
gives an error (US locale does not treat dots as date part separators), but
msgbox malformattedDatetimeConvertedToISOlike("08.09.2024 17:03:45")
gives 2024-09-08 17:03:45.
Likewise,
msgbox malformattedDatetimeConvertedToISOlike("08/09/2024 17:03:45")
gives an error (German locale does not treat slashes as date part separators), but
msgbox malformattedDatetimeConvertedToISOlike("08/09/2024 17:03:45", "en-us")
produces 2024-08-09 17:03:45
This would also handle strings like “Aug 25 2024 17:03:45”.
1 Like