Apache OpenOffice (AOO) Bugzilla – Issue 75699
[Informix ODBC] Query design converts timestamp of midnight to date only
Last modified: 2023-11-08 19:42:42 UTC
OOo 2.1 MS XPsp2 When trying to construct a timestamp (datetime) value in the query design grid to select a date with 00:00:00, the resulting SQL is converted to a date only field. #2007-03-01 00:00:00# becomes {D '2007-03-01'} instead of {TS '2007-03-01 00:00:00'} #2007-03-01 00:00:01# becomes {TS '2007-03-01 00:00:01'} Informix IN9 complains when an SQL query is formated as date only when the field in question is defined as a timestamp or datetime field. In my case, I need to query on sales records where the date field is defined as 'YYYY-MM-01 00:00:00' for each sales month. The Informix ODBC driver complains when presented with a date only format for this field. Native mode can be used as a workaround, but any changes made to the query using the query design grid translates the datetime value back to a date only format. This forces a re-edit in native mode to correct. On a side note: if the criteria of the timestamp field is paramaterized (ie. =[mydatetime]), entering a midnight time passes properly. In working with a MySQL database, this conversion doesn't seem to be an issue, as the MySQL driver allows the date only format against a datetime field and as long as the time portion of the field data is midnight, then the query succeeds.
clu->dlairmarc: without trying this out, this sounds like a driver related problem (oo seems to work fine) - 1. what driver do you use? 2. do you have the possibility to use an other driver?
Data: id acct_mo_yr acct_day value_a value_b 0 04/01/2007 00:00:00 1 2.000 3.000 1 03/01/2007 00:00:01 4 5.000 6.000 2 02/01/2007 00:00:02 7 8.000 9.000 Query 1: In the query design grid I placed: 4/1/2007 00:00:00 SQL statement generated: SELECT "id", "acct_mo_yr", "acct_day", "value_a", "value_b" FROM "Table1" WHERE ( ( "acct_mo_yr" = {D '2007-04-01' } ) ) With the embedded HSQL database, the query grid translation to a date only format produces the record number 0 without any problem. Query 2: In the query design grid I placed: 3/1/2007 00:00:01 SQL statement generated: SELECT "id", "acct_mo_yr", "acct_day", "value_a", "value_b" FROM "Table1" WHERE ( ( "acct_mo_yr" = {TS '2007-03-01 00:00:01.0' } ) ) Add a second to the time field and the query design grid translation results in the correct TS type in the SQL statement. This is what I expected with Query 1. Additionally, if the time portion wasn't specified in the criteria, I would expect it to default to 00:00:00 in the resulting SQL statement as a time stamp field to match the underlying DB field definition. Using either Informix 3.30 or Intersolv 3.11 ODBC drivers, this translation to a date only type SQL statement when the time portion is 00:00:00 causes/returns a data type mismatch error as the field being referenced is defined as being a time stamp type in the DB which the driver is respecting. It's expecting a time stamp value and complains when it's presented with something else. Close isn't good enough. (In my case, the DB has this field defined as a time stamp data type, even though the actual data is always day 1 of any given month/year with the time portion always being 00:00:00. I can't change this, I just have to work with it.) I'll attach my sample data base to demonstrate the issue.
Created attachment 44795 [details] Sample DB for Issue 75699
does this problem still occur in a current version?
I'll recheck this in the next day or so (Very busy with other issues at the moment)
I had a chance to recheck under 2.4.1 and under the 3.00 dev m22 build, the problem still exists. Input the date value as a timestamp format with the time of 00:00:00 and the query designer converts it to a simple date field w/o the time. It makes using the GUI query designer next to useless for these cases.
can anybody confirm that?
I've obtained the latest Informix SDK and will see if this still occurs with the newest ODBC drivers.
change owner
No response in 18 months @dlairmarc did new driver help?
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download Please report back the outcome of your testing, so this Issue may be closed or progressed as necessary - otherwise the issue may be Resolved as Invalid in the future.
(In reply to drewjensen.inbox from comment #10) > No response in 18 months > > @dlairmarc did new driver help? No news from OP Feel free to reopen with requested informations