Issue 108229 - Date/Time functions do not work with parameters
Summary: Date/Time functions do not work with parameters
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 3.2 RC1
Hardware: Unknown All
: P3 Trivial (vote)
Target Milestone: 3.4.0
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2010-01-11 19:45 UTC by villeroy
Modified: 2017-05-23 00:29 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
document to reproduce the bug case (3.17 KB, application/vnd.sun.xml.base)
2010-02-01 19:29 UTC, Frank Schönheit 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description villeroy 2010-01-11 19:45:33 UTC 
SELECT * FROM "X" WHERE DAYOFWEEK( :paramDay )= "X"."WeekDay" => wrong type

Same problem with other date/time functions and :parameter substitution (I
tested MONTH, HOUR)
Comment 1 r4zoli 2010-01-12 08:27:11 UTC 
I can confirm on OOo3.2RC1 linux version(only tested on it)
Comment 2 Frank Schönheit 2010-02-01 19:29:09 UTC 
Created attachment 67513 [details]
document to reproduce the bug case
Comment 3 Frank Schönheit 2010-02-01 19:34:35 UTC 
confirming:
- open the attached database document
- open the contained query by double-clicking it
=> the parameter input dialog opens
- enter a date value in the "Value" field
- press OK
=> you get an error message saying that this is no legitimate value for the
   column
   (which is wrong)
- enter a plain numeric value in the "Value" field
- press OK
=> the value is accepted
   (which is wrong)
=> the query's data view opens, but you get an error message saying something
   about an illegal type

fs->oj: I'd say the problem here is that the parser recognizes the parameter as
integer column, since the return type of DAYOFWEEK is an integer. It should,
however, recognize it as date column.
Comment 4 ocke.janssen 2011-01-04 12:42:00 UTC 
Fixed in cws dba34c
Comment 5 ocke.janssen 2011-01-14 08:14:54 UTC 
Please review. Thanks.
Comment 6 marc.neumann 2011-02-22 12:02:26 UTC 
not fixed, the result of the date parameter is wrong. In the table in the bugdoc
insert 2 as value for the dayofweek field. An in the query parameter insert
2011-02-21. The record is not found.
Comment 7 marc.neumann 2011-02-22 12:33:32 UTC 
reassign to oj
Comment 8 ocke.janssen 2011-03-01 05:58:20 UTC 
.
Comment 9 ocke.janssen 2011-03-16 09:59:09 UTC 
Please verify. Thanks.
Comment 10 marc.neumann 2011-03-23 07:51:57 UTC 
verified in cws dba34d
Comment 11 r4zoli 2011-03-25 12:07:36 UTC 
Checked in DEV300m104, OK.
Comment 12 Jeff Klopotic 2017-05-23 00:29:48 UTC 
Comment on attachment 67513 [details]
document to reproduce the bug case

This appears the same problem in different terms from my experience - date/time functions can't work with date/time strings that have auto-appended apostrophes - and this happens when date/time strings are copy-pasted into Calc.  Trying to bulk edit them out is not possible due to an additional bug that causes mangling of the string when trying to do a simple global replace of "'" for "".  Hand editing of strings in Calc does fix the problem, which appears to be an auto-correct adding of an "'" to the date/time string upon pasting.  Can this be fixed by a simple setting change?  Any help appreciated.