[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [office] Problems with DAY, MONTH and YEAR functions
hi Regina, On 15/10/2023 01:04, Regina Henschel wrote:
Hi all,Quotes and links to relevant parts of the ODF 1.3 specification are at the end of the mail.(A)The DAY, MONTH and YEAR functions use the data type DateParam, which is a pseudotype. DateParam (4.11.3) uses the wording "(interpreted as a serial number; 4.3.3)", but section Date (4.3.3) does not contain a term 'serial number'. It has a term 'serial date'.
perhaps "serial number" is a DateTime, and "serial date" is a Date? so every "serial date" is a "serial number" (with fraction 0). although no, this first idea appears to be wrong, see reply to (B).
(B)It is not clear whether a 'serial date' (4.3.3) can be non-integer. The wording 'integer part of the date serial number' in DATEVALUE (4.10.4) suggests this.
i think the "date serial number" in that sentence is a value internal to DATEVALUE, produced from the given "Text D", which may contain a time.
i wouldn't read that as implying that a "Date" as a return value or function parameter may contain a fractional time.
If 'serial date' can be non-integer, the relationship to data type Date needs to be specified, because data type Date is 'represented by an integer value'.
... i'm not sure if "serial date" is really defined anywhere, but see "6.3.9 Conversion to DateSequence ... a serial date value of subtype Date."this sentence implies to me that "serial date" may be something else than Date, i.e. DateTime.
Perhaps a different wording in DATEVALUE is needed? Or perhaps we need an additional term 'date-time serial number'?
i think given the above the "date serial number" is not the problem; the problem is that DATEVALUE always truncates; we need a DATEVALUE.ROUND to be used by YEAR.ROUND, and the definition of YEAR.ROUND needs to specify that it uses a modified conversion 6.3.15 with DATEVALUE.ROUND instead of DATEVALUE (and similar with TIMEVALUE.ROUND).
another possibility would be to define a function DATETIMEVALUE.ROUND, that could be used by both date and time functions that round... although i'm not sure if that would be allowed, see reply to (D)...
(C)The data type after conversion to pseudotype DateParam (6.3.15) is not clear. Datatype Number can have fractional digits. The DATEVALUE functions returns the data type Date (4.3.3). That is an integer value. The VALUE (6.13.34) function returns data type Number and explicitly allows fractional seconds.(D)For pseudotype DateParam it is specified, that text is converted to a 'date value'. Is 'date value' to be read as 'value of data type Date'? If yes, the conversion of text needs to result in an integer value or Error. Or is a 'date-time serial number' as mentioned in (B) intended?
yes, this is a bit odd, additionally:"* Number, then return it" -> if the number is a DateTime, then it is returned as-is, as a DateTime, may or may not be Date
this means that YEAR has to deal with rounding, in case the DateParam was Number.
except if we read it as DateParam constrains it (if it is a Number) to a Date without fractional part, then "return it" will also return in a Date of course.
(E)The Semantics part of YEAR (6.10.23) function has "Parses a date-formatted string". But the pseudotype DateParam (6.3.15) need not be text.
that is a problem, but the resolution of OFFICE-4094 already fixed it via "Returns the year portion of D ...".
(F)The Semantics part of YEAR (6.10.23) function has conversion rules for text. Because for data type DateParam "text is automatically converted to a date value" (4.11.3) it is not clear, whether "Convertion to DateParam" (6.3.15) or these special rules have to be used.
i think the first paragraph of conversion rules does not belong in YEAR function but instead in VALUE function, because of how the textual conversion is defined by 6.3.15 Conversion to DateParam, referring to DATEVALUE, referring to VALUE.
the first paragraph constrains how the text is converted to serial number.the second paragraph constrains how the serial number is converted to year, so it should be in YEAR.
I think before these problems are not solved, it is not possible to solve the "round or trunc seconds"-Problem.Kind regards, Regina ========================================================================= <quote> 4.11.3 DateParamA DateParam is a value that is either a Number (interpreted as a serial number; 4.3.3) or Text; text is automatically converted to a date value. 6.3.15</quote> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017916_715980110 <quote> 4.3.3 Date Date is a subtype of Number. Date is represented by an integer value.A serial date is the expression of a date as the number of days elapsed from a start date called the epoch. Evaluators shall support all dates from 1904-01-01 through 9999-12-31 (inclusive) in calculations, should support dates from 1899-12-30 through 9999-12-31 (inclusive) and may support a wider date range. Evaluators shall support positive serial numbers. Evaluators may support negative serial numbers to represent dates before an epoch.</quote> Quoted without 'Note' paragraphs. https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#DATE <quote> 6.3.15 Conversion to DateParam If the expected type is the pseudotype DateParam, then if value is of type:  âNumber, return it. âText, pass to DATEVALUE 6.10.4, and if non-Error, return it. If DATEVALUE would return an Error, an evaluator may attempt to convert to a Number in other ways (such as by calling VALUE 6.13.34); this is implementation-defined. If the evaluator cannot convert to Number, it returns an Error.  âLogical, the result is implementation-defined, either a Number or Error  âReference: perform conversion to scalar, then perform as above. If the cell is empty, return 0.</quote> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1018006_715980110 <quote> 6.10.23 YEARSummary: Extracts the year from a date given in the current locale of the evaluator.Syntax: YEAR( DateParam D ) Returns: Number Constraints: NoneSemantics: Parses a date-formatted string in the current locale's format and returns the year portion. If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by HOST-NULL-YEAR. Evaluators shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Four-digit year numbers following 1582 should return the year number.</quote> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#YEAR <quote> 6.10.4 DATEVALUE Summary: Returns the date serial number from given text. Syntax: DATEVALUE( Text D ) Returns: Date Constraints: NoneSemantics: This computes the serial number of the text string D, using the current locale. This function shall accept ISO date format (YYYY-MM-DD), which is locale-independent. It is semantically equal to VALUE(Date), if Date has a date format, since text matching a date format is automatically converted to a serial number when used as a Number. If the text of D has a combined date and time format, e.g. YYYY-MM-DD HH:MM:SS, the integer part of the date serial number is returned. If the text of D does not have a date or time format, an evaluator may return an Error. See VALUE for more information on date formats. The value of the serial number depends on the current epoch.</quote> https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#DATEVALUE 6.13.34 VALUE https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#VALUE To long to quote. ---------------------------------------------------------------------To unsubscribe from this mail list, you must leave the OASIS TC that generates this mail. Follow this link to all your TCs in OASIS at:https://www.oasis-open.org/apps/org/workgroup/portal/my_workgroups.php
-- Michael Stahl Senior Software-Entwickler LibreOffice âââ allotropia software GmbH Versmannstr. 4 20457 Hamburg Germany âââ michael.stahl@allotropia.de https://www.allotropia.de âââ Registered office: Hamburg, Germany Registration court Hamburg, HRB 165405 Managing director: Thorsten Behrens VAT-ID: DE 335606919 âââ
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]