The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 . (WIS 10901)

[Former Member]

Hi Experts,

I am using BO 3.2, and Connection DB2.

I am facing the error "

The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

. (WIS 10901)" while using the object in webi for a report..

Please find the existing code below.

SELECT

  date(DB2ADMIN_It_PRO_pck.START_DATE )

FROM

  DB2ADMIN.It_PRO  DB2ADMIN_IA_PRO_pck

I tried to fix this issue by updating the code as

SELECT

  date(DB2ADMIN_It_PRO_pck.START_DATE )

FROM

  DB2ADMIN.It_PRO  DB2ADMIN_IA_PRO_pck

WHERE

  ( DATE(( DB2ADMIN_It_PRO_pck.START_DATE)) = 'DD-MM-YYYY'  ) which is not working.

The object data type in universe is in DATE and code "  date(DB2ADMIN_It_PRO_pck.START_DATE )"

Can anyone check what i can do?

[Former Member]

Hi Priya,

Put the date in below format, it think it will work:

( DATE(( DB2ADMIN_It_PRO_pck.START_DATE)) = 'YYYY-MM-DD'  )

In order to check the desired format as per the database, you can check value set for USER_INPUT_DATE_FORMAT parameter in <database>.prm file.

Also check if the datatype of START_DATE is timestamp or date.

You can check the PRM file details by testing the connection and checking the path for PRM file in below message.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

I tried ( DATE(( DB2ADMIN_It_PRO_pck.START_DATE)) = 'YYYY-MM-DD'  ) which is also not working getting the same error. Please find below for more info..

I am trying to fix this issue in UNIVERSE side, Please find below for more informations

This is code available in Universe Properties "date(DB2ADMIN_It_PRO_pck.START_DATE)

This is SQL from Universe Query Pannel

SELECT DISTINCT

  date(DB2ADMIN_It_PRO_pck.START_DATE)

FROM

  DB2ADMIN.IT_PRO  DB2ADMIN_It_PRO_pck

　

Where IT_PRO_PCK is a ALIAS Table and it Joing With IT_PRO which is real table.

In both the tables, i am, referring to the column START_DATE which has datatype CHAR defined in tables.

So, I tried to CAST the existing code to date(CAST(DB2ADMIN_It_PRO_pck.START_DATE AS DATE)) which is also not working.

I am using BO 3.2 and connection type DB2.

Please let me know if you need somemore info..

[Former Member]

Hi Priya,

When you try date(DB2ADMIN_It_PRO_pck.START_DATE) what format do you get?

You can try something like this:

date(to_date(DB2ADMIN_It_PRO_pck.START_DATE,'YYYY-MM-DD')) = 'YYYY-MM-DD'

Hope it will help.

Regards,

Yuvraj

[Former Member]

Can you explain are you making the code change in report or universe ? Is this a webi report query?

[Former Member]

Hi Mishra,

I am trying to fix this issue in UNIVERSE side, Please find below for more informations

This is code available in Universe Properties "date(DB2ADMIN_It_PRO_pck.START_DATE)

This is SQL from Universe Query Pannel for the object

SELECT DISTINCT

date(DB2ADMIN_It_PRO_pck.START_DATE)

FROM

DB2ADMIN.IT_PRO  DB2ADMIN_It_PRO_pck

Please note the object is in DIMENION and data type DATE in universe properties.

Where IT_PRO_PCK is a ALIAS Table and it Joing With IT_PRO which is real table.

In both the tables, i am, referring to the column START_DATE which has datatype CHAR defined in tables.

So, I tried to CAST the existing code to date(CAST(DB2ADMIN_It_PRO_pck.START_DATE AS DATE)) which is also not working.

I tried to update SQL in Universe as below as well which is also not working.

SELECT

  date(DB2ADMIN_It_PRO_pck.START_DATE )

FROM

  DB2ADMIN.It_PRO  DB2ADMIN_IA_PRO_pck

WHERE

  ( DATE(( DB2ADMIN_It_PRO_pck.START_DATE)) = 'YYYY-MM-DD'  )

I am using BO 3.2 and connection type DB2.

Please let me know if you need somemore info..