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
Status: UNCONFIRMED
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:
URL:
Keywords: needhelp, needmoreinfo
Depends on:
Blocks:
 
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: ---


Attachments
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
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.
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 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.