Apache OpenOffice (AOO) Bugzilla – Issue 25146
TimeValue() function using Query Designer with ODBC out by two days
Last modified: 2006-05-31 14:29:06 UTC
Hi, I've noticed the TimeValue() scalar function is incorrect by two days when used in the Query Designer accessing an MS Access database using ODBC. To reproduce this: Set up the attached database Example.mdb as a data source using ODBC. For this exercise I will also call the data source Example in OpenOffice.org Press F4 to open the data source window Double click on the Example data source to open Right click on Queries Select New Query (Design View) Click on Add to add the Test table Click on Close In the first field enter TimeValue('8:30') (No fields are acutally required from the table. The expression result is repeated for each row in the underlying table. In this case one row.) Click on the Run Query button You will see the result of the expression is -1.65. This should be 0.35 Frank from the dba mailing list in the following quote, I think has indicated where the problem may lay. "... 2 is exactly the offset between the SQL standard "null date" used in the database world (1.1.1900) and the standard "null date" used in the other OOo applications (30.12.1899). So this sounds like we have some kind of collision here, and it should at least be investigated." The -1.65 would appear to be as a result of -2 for the null date issue with 0.35 added back on for the actual time value. Hope this helps. Kelvin
Created attachment 12922 [details] MS Access database to be used to reproduce problem
Hi, I can reproduce this. bye Marc
reassign
Hi Kelvin, the problem here is that we use the 01/01/1999 as NULL date. MS uses 12/30/1899. So when you compare the double values, they are different but when you compare the timestamp value then all is fine :-) I fixed the bug in that way that our parser now regconize the timevalue and datevalue function and so it can distinguish the return type and format the value in the correct way. Best regards, Ocke
.
reopen
fixed in cws insight01 -> please verify
verified in cws - (like oj has described)
verified in master (680m50-8)
CLU->kelvine: please verify - versions here: http://download.openoffice.org/680/index.html
Hi, I just retested this. Interesting. When the query is run in the m51 version of OOo the column now displays as a Date/Time field. It appears as "30/12/99 08:30". Is this right or wrong, I can't really say. However if I change the format to a number it becomes -1.65 again. I personally would have thought that 8:30 should be a positive number. That is the decimal component is 8.5/24 = 0.35.... When I think this through I think (correct me if I'm wrong) this one has to simply be put down as a difference between the way OpenOffice.org and MS Access work. If I do the query in MS Access I get 0.35. If I do it in OOo I get -1.65. IMHO this is not right. The two products should produce the same result if they are issuing the same query. The fact that MS Access and OpenOffice.org use a different NULL date should be totally transparent to the user. Two products producing different results for the same query is not something anyone would expect, and will lead to difficult to debug programming errors. Thanks Kelvin