Apache OpenOffice (AOO) Bugzilla – Issue 109652
CDateFromIso not working for all ISO dates
Last modified: 2017-03-10 20:31:58 UTC
I don't know if Basic errors reported here but CDateFromIso doesn't convert all dates from the ISO date format correctly to the internal Basic date format. Examples: 1) Print CDateFromIso("2002-09-30") gives correctly 30.09.2002 (fi) or 09/30/2002 (us) etc. 2) Print CDateFromIso("2002-11-30") gives wrongly 01/30/2002. 3) Print CDateFromIso("2002-10-30") gives errormessage. Maybe it works with short form of ISO dates but ISO standard specify primarily this dashed form so it is certaily the ISO format. Paremman huomisen toivossa Risto
Help F1 on CDateFromIso shows one example, in 4 digits without dashes. Help does not say which other formats are supported, if any. Help says that year may be 2 or 4 digits, but in fact 2-digits years are supposed 19xx. So, so you had better use only the 4-digits format, and only digits.
Let see where we are now: 1) User is informed that this funtion make conversion from ISO date to internal format. 2) We have good reason to believe that user know ISO format 2010-03-02 if any. 3) There is hint in example that function may work also with lesser known ISO format 20100302 4) Now we test if beginners are clever enought that he find out that this fucntion is not at all meant for that well known ISO format. 5) With the same resources (labour * time) what we are here using this chatting we have had allready write some piece of information to the help page (and to ohter documentations) so beginners may know more. Maybe the change in the source code take too much resources and that I too must accepted. That's all I will say about this case here. Regards Risto
also defect under Debian GNU/Linux in version 3.2.1 (Vanilla)
.
Calc-functions need dates in ISO-format with hyphens. So broken CDateFromIso leads to inconsistence.
Copy-pasting the example from the manual at http://help.libreoffice.org/Basic/CDateFromIso_Function_Runtime : CDateFromIso("20021231") results in the error message: #Name?. It doesn't seem possible to coax a sensible response out of this function.
Copy-pasting the example from the manual at http://help.libreoffice.org/Basic/CDateFromIso_Function_Runtime : CDateFromIso("20021231") results in the error message: #Name?. It doesn't seem possible to coax a sensible response out of this function. LibreOffice 3.4.4 OOO340m1 (Build:402)
(In reply to charlesli from comment #7) > CDateFromIso("20021231") > results in the error message: #Name?. > LibreOffice 3.4.4 Not reproduce with AOO 4.1.0 CDateFromIso("20021231") returns 31/12/2002
insertText(chr(9) + "Scheduled" + chr(9) + columnScheduled + chr(9) + "Ultimate budget payment due" + chr(9) + chr(9) + CDateFromISO(columnFinalBudget)) fails with ACTION NOT SUPPORTED INVALID PROCEDURE CALL This fails for dates in October 2021. It works for dates before that. Removing the CDateFromISO() wrapper completes job. This is a macro which extracts from a View and prints to an .odt writer file. So, there is a serious untested corner condition or whatever in CDateFromISO. Yes, I am a bit ahead of the calendar, but not much, considering that the application is for a loan repayment schedule. There are plenty other CDateFromISO conversions in the same macro. They all work, but then are not in the offending month. I haven't checked whether the error continues AFTER October 2021!
(In reply to D Denny from comment #9) > insertText(chr(9) + "Scheduled" + chr(9) + columnScheduled + chr(9) + > "Ultimate budget payment due" + chr(9) + chr(9) + > CDateFromISO(columnFinalBudget)) > fails with > ACTION NOT SUPPORTED INVALID PROCEDURE CALL How is columnFinalBudget defined? Where you get it from? Nobody have posted a working sample macro or better a document with an embedded macro to show the problem, what is expected and what is get. And nobody says which LOCALE setting is used! Sub Main DateTest_mr( "2021-10-11" ) End Sub Sub DateTest_mr( s As String ) InPutBox( "Date Conversion", "Result:", "String: " & s ) InPutBox( "Date Conversion", "Result:", "CDate( String ): " & CDate( s ) ) InPutBox( "Date Conversion", "Result:", "CDateToISO( String ): " & CDateToISO( s ) ) InPutBox( "Date Conversion", "Result:", "CDateToISO( CDate( String ) ): " & CDateToISO( CDate( s ) ) ) InPutBox( "Date Conversion", "Result:", "CDateFromISO( CDateToISO( CDate( String ) ) ): " & CDateFromISO( CDateToISO( CDate( s ) ) ) ) End Sub Result: (Locale: English (UK) ) But remember: This shows only the String representations! String: 2021-10-11 CDate( String ): 11/10/2021 CDateToISO( String ): 20211011 CDateToISO( CDate( String ) ): 20211011 CDateFromISO( CDateToISO( CDate( String ) ) ): 11/10/2021
Created attachment 85974 [details] in answer to "s columnFinalBudget defined? Where you get it from?" this is a quick backtrack showing the derivation of the data item in question. I have elicited that dates beyond October 2021 also fail. There is a discrepancy in the locale settings in my AOO options. I will update this shortly. Most settings are default english uk except the default language for writer docs which is english usa for some reason.
Your attached file is an SQL query. What is the relationship with this current issue?
Created attachment 85975 [details] macro for test purposes this is a cut down macro showing field derivation
Created attachment 85976 [details] output in full with dates before october 2021 I found originally that my locale was UK English but the document language was USA English. In case this mattered I tested first with all set to USA. Then I reset all to UK. In both cases CDateFromISO() fails with dates >= 01 October 2021. So I guess that locale is not an issue.
Created attachment 85977 [details] this is the output if I change the date of the last Budget to 01 October 2021 This shows the text display for the two fields but stops on the first attempt that CDateFromISO is given 01 Oct 2021.
My AOO version is 4.1.1 has this been fixed in a subsequent version?
(In reply to oooforum (fr) from comment #12) > Your attached file is an SQL query. > What is the relationship with this current issue? I was showing the derivation of the data in answer to the question where does the data originate... please see other attachments
(In reply to mroe from comment #10) > (In reply to D Denny from comment #9) > > insertText(chr(9) + "Scheduled" + chr(9) + columnScheduled + chr(9) + > > "Ultimate budget payment due" + chr(9) + chr(9) + > > CDateFromISO(columnFinalBudget)) > > fails with > > ACTION NOT SUPPORTED INVALID PROCEDURE CALL > > How is columnFinalBudget defined? Where you get it from? > > > Nobody have posted a working sample macro or better a document with an > embedded macro to show the problem, what is expected and what is get. > And nobody says which LOCALE setting is used! > > Sub Main > DateTest_mr( "2021-10-11" ) > End Sub > > Sub DateTest_mr( s As String ) > InPutBox( "Date Conversion", "Result:", "String: " & s ) > InPutBox( "Date Conversion", "Result:", "CDate( String ): " & CDate( s ) ) > InPutBox( "Date Conversion", "Result:", "CDateToISO( String ): " & > CDateToISO( s ) ) > InPutBox( "Date Conversion", "Result:", "CDateToISO( CDate( String ) ): " & > CDateToISO( CDate( s ) ) ) > InPutBox( "Date Conversion", "Result:", "CDateFromISO( CDateToISO( CDate( > String ) ) ): " & CDateFromISO( CDateToISO( CDate( s ) ) ) ) > End Sub > > > Result: (Locale: English (UK) ) > But remember: This shows only the String representations! > > String: 2021-10-11 > CDate( String ): 11/10/2021 > CDateToISO( String ): 20211011 > CDateToISO( CDate( String ) ): 20211011 > CDateFromISO( CDateToISO( CDate( String ) ) ): 11/10/2021 I tried both UK English and USA English (just in case). Now all set at UK. Same result. Thanks.
I mean, same result, still choking on Oct 2021.
Created attachment 85978 [details] fragment of basic macro showing where cdatefromiso can't be used I't clear that CDateFromISO() is not usable IN THIS CONTEXT for dates after 30/09/2021. I have run separately your sample sub and proven to myself that CDateFromISO() runs perfectly happily on its own. Therefore I have come to the "intuitive" conclusion that my procedures have stress tested CDateFromISO() to destruction in certain circumstances. I have verified that the data items concerned are clean and derive cleanly from the SQL database (HSQLDB 2.3.4) There arise two possiblities. 1. that this is a bug in AOO 4.1.1 which has been cleared in later versions. 2. that it needs investigation. My guess is that this is a memory management issue within AOO/Java: that memory allocation is breaking the calendar table. If so, this cannot be reproduced by a simple test macro: it needs stress testing. I am happy to help as an end user but I fear I cannot go further as I have no expertise in AOO internals. I am happy to help by alpha testing any suggested changes. Thanks.
(In reply to D Denny from comment #20) > I have run separately your sample sub and proven to myself that > CDateFromISO() runs perfectly happily on its own. The simply question is: Which string you get from columnFinalBudget = RowSet.getString(16) for dates > 2021-09-30? If the column is defined as DATE you should better read the date rather the string representation. So your macro will be independent from any uncontrolled string conversion. AND: Please test your macro with Option Explicit and define your variables to get sure that there is not an unwanted conversion! Dim columnFinalBudget As String For the original reported bug I suggest to change the help for »CDateFromISO«, that it awaits a String in the form of "YYYYMMDD". This is what the counter part »CDateToISO« supplies.
(In reply to mroe from comment #21) > (In reply to D Denny from comment #20) > > I have run separately your sample sub and proven to myself that > > CDateFromISO() runs perfectly happily on its own. > Firstly, thanks very much for your comprehensive help and careful attention! Gratefully received. > The simply question is: > Which string you get from > columnFinalBudget = RowSet.getString(16) > for dates > 2021-09-30? > YYYY-MM-DD : example 2021-10-05 > If the column is defined as DATE you should better read the date rather the > string representation. So your macro will be independent from any > uncontrolled string conversion. please see below ** > > AND: Please test your macro with > Option Explicit > and define your variables to get sure that there is not an unwanted > conversion! > Dim columnFinalBudget As String Applied ** changed .getString to getDate as dim columnFinalBudget as date ' string columnFinalBudget = RowSet.getDate(16) ' String(16) print columnFinalBudget resulting in "Incorrect Property Value" I think that because the date returned from the SQL VIEW is YYYY-MM-DD it will be necessary to convert it another way, by string removing the "-" separators. Or, I could change the VIEW result definition. Currently specified as DATE. So it appears (also re your comment below) that, while SQL is writing dates in the format YYYY-MM-DD, Basic is expecting the format YYYYMMDD. Maybe I should be trying getXXX first. I'll have a look at that. [... Work In Progress, BRB ...] > > > For the original reported bug I suggest to change the help for > »CDateFromISO«, that it awaits a String in the form of "YYYYMMDD". This is > what the counter part »CDateToISO« supplies.
> > The simply question is: > > Which string you get from > > columnFinalBudget = RowSet.getString(16) > > for dates > 2021-09-30? > > > > YYYY-MM-DD : example 2021-10-05 For InPutBox( "Date Conversion", "Result:", "CDateFromISO( String ): " & CDateFromISO( "2021-10-05" ) ) I get the same “Action not supported. Invalid procedure call.” No problem with InPutBox( "Date Conversion", "Result:", "CDateFromISO( String ): " & CDateFromISO( "20211005" ) ) > dim columnFinalBudget as date ' string > columnFinalBudget = RowSet.getDate(16) ' String(16) > print columnFinalBudget > > resulting in > > "Incorrect Property Value" You will not get a value with type Date – you get a struct com.sun.star.util.Date! http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdb/XColumn.html#getDate http://www.openoffice.org/api/docs/common/ref/com/sun/star/util/Date.html Dim newDate As New com.sun.star.util.Date newDate = RowSet.getDate(16) print newDate.Year, newDate.Month, newDate.Day If you want to use the string representation then use Dim columnFinalBudget As String Dim dateString As String columnFinalBudget = RowSet.getString(16) 'Now you can convert it first to a legal ISO representation with the functions above 'or with string manipulations 'or simply use dateString = CDate( columnFinalBudget ) For me at this point: EoD. Because it isn't about the issue anymore.
Thanks! Best regards David
As says in comment 1: the format must be 4 digits without dashes. Conform as F1 help