Issue 75699 - [Informix ODBC] Query design converts timestamp of midnight to date only
Summary: [Informix ODBC] Query design converts timestamp of midnight to date only
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.1
Hardware: All Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: needhelp, needmoreinfo
Depends on:
Reported: 2007-03-24 07:53 UTC by dlairmarc
Modified: 2013-08-07 15:45 UTC (History)
2 users (show)

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

Sample DB for Issue 75699 (3.83 KB, text/plain)
2007-05-02 03:27 UTC, dlairmarc
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dlairmarc 2007-03-24 07:53:46 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.
Comment 1 christoph.lukasiak 2007-04-30 14:08:56 UTC
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?
Comment 2 dlairmarc 2007-05-02 03:25:06 UTC

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

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.
Comment 3 dlairmarc 2007-05-02 03:27:43 UTC
Created attachment 44795 [details]
Sample DB for Issue 75699
Comment 4 christoph.lukasiak 2008-06-17 13:44:36 UTC
does this problem still occur in a current version?
Comment 5 dlairmarc 2008-07-06 06:40:51 UTC
I'll recheck this in the next day or so (Very busy with other issues at the moment)
Comment 6 dlairmarc 2008-07-07 04:47:28 UTC
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.
Comment 7 christoph.lukasiak 2008-09-01 16:09:15 UTC
can anybody confirm that?
Comment 8 dlairmarc 2008-09-03 05:37:44 UTC
I've obtained the latest Informix SDK and will see if this still occurs with the
newest ODBC drivers.

Comment 9 christoph.lukasiak 2008-09-24 12:44:14 UTC
change owner
Comment 10 drewjensen.inbox 2010-03-07 15:58:11 UTC
No response in 18 months

@dlairmarc did new driver help?

Comment 11 Rob Weir 2013-02-02 02:57:49 UTC
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

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