Unexpected behavior while writing Calc Functions

users
#1

I wrote a simple function

Function mydate As Date
   mydate = CDate(100)
End Function

I then called this function from a Calc document

=myDate()

Finally, I directly accessed the cells with the dates from a macro similar to the following code:

Print ThisComponent.Sheets(0).getCellByPosition(0, 1).Value

The cell displayed the value as I expected, but the returned value was zero.

Turns out that dates are converted to strings when returned to Calc. Oops. The proper thing to do (if you want it to work as expected) is to return a numeric value.

Side note, this behavior is the same in the Apache and the Libre version of OO, so this has probably been there a long time.

#2

Calc does not know any dates nor times. It's all about doubles and text.

#3

Although that is true, I expected a date returned from a function would return as a double, not text. Internal Date functions in Calc appear to return as a double and then it is smart enough to display it as a date.... or am I mistaken?

My first thought was to poke through the code to see where that transition occurs, but I have no clue where to even begin to find that...

#4

Am 16.07.2012 06:11, Andrew Douglas Pitonyak wrote:

Calc does not know any dates nor times. It's all about doubles and text.

--
View this message in context:
http://nabble.documentfoundation.org/Unexpected-behavior-while-writing-Calc-Functions-tp3995669p3995674.html

Sent from the Users mailing list archive at Nabble.com.

Although that is true, I expected a date returned from a function would
return as a double, not text. Internal Date functions in Calc appear to
return as a double and then it is smart enough to display it as a
date.... or am I mistaken?

Sorry, no magic conversion. The double is displayed as date/time when the cell is formatted accordingly. The cell value remains a double.
In the context of number format "General", certain input strings are recognized as date/time and number format "General" displays one particular number format for the recognized data type. Once you applied some number format other than the "General" one, any number will be shown in that format.
Nevertheless, we can queryContentCells for c.s.s.sheet.CellFlag.DATETIME because the application needs to keep track of those "special doubles" because the base date for the entire document may be changed by the user.

#5

Hi Andrew,

Andrew Douglas Pitonyak schrieb:

I wrote a simple function

Function mydate As Date
   mydate = CDate(100)
End Function

I then called this function from a Calc document

=myDate()

Finally, I directly accessed the cells with the dates from a macro
similar to the following code:

Print ThisComponent.Sheets(0).getCellByPosition(0, 1).Value

The cell displayed the value as I expected, but the returned value was
zero.

Turns out that dates are converted to strings when returned to Calc.
Oops. The proper thing to do (if you want it to work as expected) is to
return a numeric value.

I'm not sure about that. "date" is a special Basic data type, it is not type "double". The "Programming Guide" tells vaguely "StarOffice
Basic uses an internal format that permits comparisons and mathematical operations on date and time values."

I do not find informations about mapping type "date" to UNO. The Developer's Guide has a mapping UNO -> Basic, but I miss the other way round. There seems to be some conversions already in Basic. If your function returns double not date, then you get the serial number of the date/time.

It is more a documentation issue, than a real bug.

Kind regards
Regina

#6

Hi :slight_smile:
I am pretty sure that Date is an Sql data-type, just like double or text but perhaps it got tweaked-off somewhere in the embedded HsqlDb?

Andrew is almost never wrong and Regina is never wrong so i'm at a loss about this atm. 
Regards from
Tom :slight_smile:

#7

Hi :slight_smile:
I am pretty sure that Date is an Sql data-type, just like double or text but perhaps it got tweaked-off somewhere in the embedded HsqlDb?

Andrew is almost never wrong and Regina is never wrong so i'm at a loss about this atm.
Regards from
Tom :slight_smile:

Nobody is never wrong. Not even me… :stuck_out_tongue:

Kind regards

Johnny Rosenberg
ジョニー・ローゼンバーグ

#8

<<SNIP>>
> Nobody is never wrong. Not even me… :stuck_out_tongue:
> Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ
<<snip>>

Cute.
Joe Conner, Poulsbo, WA USA

#9

Hi Tom,

Tom Davies schrieb:

Hi :slight_smile: I am pretty sure that Date is an Sql data-type, just like
double or text but perhaps it got tweaked-off somewhere in the
embedded HsqlDb?

This is not about HSQL but about Basic.

Andrew is almost never wrong and Regina is never wrong so i'm at a
loss about this atm.

There are lot of areas, where I likely wrong. And in Basic I'm sure no expert.

I have looked a little bit into the code. It seems, that a Basic type 'date' is the same as com::sun::star::bridge::oleautomation struct Date.
But I do not find, where the conversion to string happens.

Kind regards
Regina

#10

I know that I can use a SQL Date type, but the regular Date type behaves like a double in every case that I have tested.... Note that I cannot say that I have tested some of the odd cases such as setting the date a day or two before some big jump based on date changes in history. I have special routines to convert between the SQL struct. I just did not expect a Basic Intrinsic Date to convert to a string. I expected it would auto-convert to Double on return.

If the Basic internals is using a struct rather than a Double as I thought, then it makes much more sense that it would convert to a string.

I think that I need to document the behavior.