We are working on a project to convert some of our data sources from SYBASE to DB2. However, we are experiencing a problem when we try to select on any of the date fields from our DB2 data sources.

Here is a simple example of code that now causes a problem for us:

TABLE FILE CGX_SPONSOR_CODE_TBL
PRINT SPONSOR_CODE DEACTIVATE_DATE MODIFY_DATE ADD_DATE LASTUPD
WHERE ADD_DATE GE 07012009
IF RECORDLIMIT EQ 50
END

Here is the error message that we receive:

(FOC1400) SQLCODE IS -180 (HEX: FFFFFF4C)
: [22007] [IBM][CLI Driver][DB2/LINUXX8664] SQL0180N The syntax of the st
: ring representation of a datetime value is incorrect. SQLSTATE=22007
(FOC1407) SQL FETCH CURSOR ERROR. : CGX_SPONSOR_CODE_TBL

Has anyone experienced similar problems and know the solution? I have seen a few postings on the Internet about this type of problem but can't figure this out as yet.

In the master, we are defining all of the date fields using the DATE format:

FILE=CGX_SPONSOR_CODE_TBL, SUFFIX=DB2 ,$
SEGMENT=CGX_SPONSOR_CODE_TBL,
SEGTYPE=S0 ,$
FIELDNAME=SPONSOR_CODE,ALIAS=SPONSOR_CODE,
A4 ,A4 ,$
...
FIELDNAME=ADD_DATE,ALIAS=ADD_DATE,
MDYY ,DATE ,$

We can write define code that adds or subtracts to our date fields (like the simple code below) and this works perfect, without any problems:

DEFINE FILE CGX_SPONSOR_CODE_TBL
DEACTIVE2/MDYY = DEACTIVATE_DATE + 10;
MODIFY_DATE2/MDYY = MODIFY_DATE - 50;
ADD_DATE2/MDYY = ADD_DATE + 100;
END

TABLE FILE CGX_SPONSOR_CODE_TBL
PRINT DEACTIVATE_DATE DEACTIVE2 MODIFY_DATE MODIFY_DATE2 ADD_DATE ADD_DATE2
IF RECORDLIMIT EQ 50
END

We just can't select on any of the date fields. Most of the programs that could be impacted are simple user MRE programs where it might be difficult to set up define logic to possibly avoid the SQLSTATE error. We should be able to select on a date field in the table section of a report. Thanks.

WebFOCUS 7.6.10, DB2 9.5 / Linux

This message has been edited. Last edited by: chandy,

quote:

The syntax of the string representation of a datetime value is incorrect

Your are using a DATE constant (07012009) when filtering on ADD_DATE but your database expects a DATE-TIME value. Try using DT() and see if it helps:

...
WHERE ADD_DATE GE DT(20090701 00:00:00)
..

Thanks njsden for the quick reply. I ran the code below as you suggested:

...
WHERE ADD_DATE GE DT(20090701 00:00:00)
...

But get an error:

ERROR AT OR NEAR LINE 4 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT:
20090701000000000
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

I'll keep trying and we are checking with our DBAs so see if they have any suggestions.

Try putting single quotes around your date value. If that doesn't work, include slashes or dashes to separate the parts of the date.

WHERE ADD_DATE GE '07012009'

or

WHERE ADD_DATE GE '07/01/2009'

Thanks Dan for the quick reply. I had previously tried formatting the FOCUS where statement as you suggested (WHERE ADD_DATE GE '07012009' or '07/01/2009') but still got the SQL STATE error message.

We SOLVED this problem with a define statement in the master file description. The users told us they need to select data based upon the most recent update date (either deactivate date, modify date or add date) and we added the following define to the master:

DEFINE LASTUPD_TMP/A8MDYY = IF DEACTIVATE_DATE NE '' THEN DEACTIVATE_DATE
ELSE IF MODIFY_DATE NE '' THEN MODIFY_DATE
ELSE ADD_DATE;
DEFINE LASTUPD/MDYY = LASTUPD_TMP;

This allows our users to select based upon the most recent update date using:

WHERE LASTUPD GE 07012009 ('07012009' and '07/01/2009' also work now)

quote:

In the master, we are defining all of the date fields using the DATE format:

Did you generate the metadata (mas and acx) from the WebFOCUS Server Console's metadata screen or did you hard-code the mas? I would not hard-code.

This is what a DB2 Date field looks like in our environment:

FIELDNAME=PERIOD_START_DT, ALIAS=PERIOD_START_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $

This is how we filter on the date field:

WHERE PERIOD_START_DT EQ '2010-07-31'

This also works:

WHERE PERIOD_START_DT EQ '2010/07/31'

So does this:

WHERE PERIOD_START_DT EQ '20100731'

Thanks everyone for your input and assistance. I solved this problem based upon your suggestions and things I found on the ibi site.

The reason that njsden's solution (WHERE ADD_DATE GE DT(20090701 00:00:00)) didn't work for me previously is because the master had been set up like this:

FIELDNAME=ADD_DATE, ALIAS=ADD_DATE,
MDYY ,DATE ,$

I changed the master to correctly report the DB2 timestamp data as it actually exists in the database. The DT() function now works properly. Other helpful functions (like HDATE) are now working as well. The ALIAS (below) refers to this field as ADD_DATE because this is how it is named in the database.

FIELDNAME=ADD_DATE_TIME ,ALIAS=ADD_DATE,
HYYMDm ,HYYMDm ,MISSING = ON,$

I used the same (year through milisecond) format for the other two timestamp fields in the database and then created three defines in the master to refer to the three date fields that our users are already coding for in their existing MRE Report Assistant reports. We also have a define for a field named LASTUPD, which our MRE users sometimes select on:

DEFINE ADD_DATE/MDYY = HDATE(ADD_DATE_TIME,'MDYY');
DEFINE MODIFY_DATE/MDYY = HDATE(MODIFY_DATE_TIME,'MDYY');
DEFINE DEACTIVATE_DATE/MDYY = HDATE(DEACTIVATE_DATE_TIME,'MDYY');
DEFINE LASTUPD/MDYY = IF DEACTIVATE_DATE NE '' THEN DEACTIVATE_DATE
ELSE IF MODIFY_DATE NE '' THEN MODIFY_DATE
ELSE ADD_DATE;

Below is how the data looks for the defined date fields and the the three timestamp fields. If a user wants to report on, or select on, a date field, or a timestamp field, they will be able to.

DEACTIVATE MODIFY_DATE ADD_DATE DEACTIVATE_DATE_TIME MODIFY_DATE_TIME ADD_DATE_TIME
_DATE
06/21/2010 06/21/2010 06/21/2010 2010/06/21 15:37:18.072000 2010/06/21 15:37:02.000000 2010/06/21 00:00:00.000000
06/21/2010 06/21/2010 06/21/2010 2010/06/21 10:04:01.904000 2010/06/21 10:03:57.000000 2010/06/21 00:00:00.000000
01/22/2010 01/22/2010 09/16/1999 2010/01/22 00:00:00.000000 2010/01/22 00:00:00.000000 1999/09/16 00:00:00.000000

As my company converts other data sources from Sybase to DB2, I'll keep the things I learned here in mind and I might also argue that we use DB2 date fields (and not timestamp fields) in our databases. In many cases, I don't believe report users need to look at the exact time of day that a customer's account was opened, changed or closed, etc. - though I realize timestamps are needed for auditors, time reporting systems and perhaps for programmers who might want to debug a process.

To answer Francis' question: I hard-coded the changes I made to the master (in our development envrionment). I am not aware of the WebFOCUS Server Console's Metadata screen. I am currently an MRE user/developer and don't have access to Developer's Studio (and perhaps other tools that might help me). I used to have Developer's Studio at my old company, but don't have it currently. Most of my FOCUS experience has been with mainframe FOCUS (VM) and then manually editing or creating WebFOCUS .fex's.