Problems with DAY, MONTH and YEAR functions

[Regina Henschel <regina.henschel@libreoffice.org>]

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'.

(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.

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'.

Perhaps a different wording in DATEVALUE is needed? Or perhaps we need 
an additional term 'date-time serial number'?

(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?

(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.

(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 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 DateParam
A 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 YEAR
Summary: Extracts the year from a date given in the current locale of 
the evaluator.
Syntax: YEAR( DateParam D )
Returns: Number
Constraints: None
Semantics: 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: None
Semantics: 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.