Want to remove timestamp from date for prompt (DB2)

building semantic layer
1

I have a universe that is used to track Issue Tickets for ATMs. I have a few data objects (Date/Time Ticket Open) and (Date/Time Ticket Closed). Both of these reference fields in a DB2 database and are stored as timestamp.

Here is my issue, my users like to query off both of these fields. However, they can only do so if they enter a criteria as a constant, if they tried a prompt they get the following error.

Bottom line - my user would like to enter a date 05/10/2006 in a prompt and see the results has 05/10/2006 10:20:05 am

Database error: [IBM][CLI Driver][DB2] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 . Contact your administrator or database supplier for more information. (Error: WIS 10901) (Error: INF )

A few other items: I am using BO version 6.1.3 - and my users use InfoView.

Thank you in advance for your help.

Justgettingstarted (BOB member since 2007-07-09)

2

Hi,
From your post i could understand as , your users wants to enter Date/Time Ticket Open, Date/Time Ticket closed.
So do they want to enetr teh date along with the time stamp or only the dates.

From teh error message it can be said that , the date type has some problem.

Again i have not worked on DB2, just doing the common understanding.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

3

It sounds to me like you need to create additional objects in the universe that strip off the timestamp, so that these objects can be used in the conditions instead.

In some other databases, the function is known as trunc

For DB2, you might find this webpage helpful;
http://www.samspublishing.com/articles/article.asp?p=174097&seqNum=2&rl=1

As far as I can gather (I don’t use DB2), you would use syntax such as this;

trunc_timestamp(tablename.columnname,ddd)

Note that for timestamps of midday or beyond, it seems that it rounds up to the next day, so you’d need some Case statement syntax to handle that.

jac :australia: (BOB member since 2005-05-10)

4

[Moderator Note: Moving to Semantic layer forum.]

jac :australia: (BOB member since 2005-05-10)

5

Hai Jac

Login into the designer,–> Open the Universe-Right Click on the object—select Object Format- Change the format to display only date

vvv (BOB member since 2006-11-07)

6

Hi vvv

I don’t think that will help in this situation, as the requirement to strip out the date is related to how data is selected, not displayed.

Thankyou though!!

jac :australia: (BOB member since 2005-05-10)

7

In the universe for DB2 just put Date() on the object and this will force it to just be a date and no time

lula813 (BOB member since 2005-07-05)

8

Thanks everyone for your help. As it stands now, the user wants to enter a date and then have the results display the entire timestamp. As stated before, this works when you create a query criteria and put the requested date as a constant instead using as a prompt.

From additional research, I am thinking the calander object is what is causing my issues.

Does anyone know if there is a way to keep the calander object from appearing?

Thanks again…

Justgettingstarted (BOB member since 2007-07-09)

9

If you’re using a prompt change it from FREE to CONSTRAINED…

Eileen King :us: (BOB member since 2002-07-10)

10
  1. Change your object Type to Character instead of date
  2. format the object way you want to_char(Table.date,‘YYYY/MM/DD’)
  3. If you need to compare the above LOV against date column in query then to_date(@prompt(,),‘YYYY/MM/DD’)

Above workaround is working for me in Oracle db. Check similar way you can thimnk in DB2 also…

Hope this will helps you…

grkalai :india: (BOB member since 2007-06-11)

11

In DB2 you need to do DATE(table.date) to removed the timestamp…

Eileen King :us: (BOB member since 2002-07-10)

12

JustGettingStarted, what version (OS and release) of DB2 are you trying to do this against? I am having a similiar problem and have found something interesting.

Using BOXIR2SP2 and DB2 connect enterprise 8.2. Two seperate universes against seperate sources of data using DB2. One is DB2 UDB 8.2 on an AIX box, the other is 7.1 on a mainframe.

Going against DB2 on the AIX box, any object used in the query filter section of Webi that is linked to a field defined as Timestamp on DB2 I can use just the date with that object and do not get an error. Also, in my report it will display the full date and time.

When I try to do the same against data on mainframe DB2, I get the same error as you. For one reason or another I cannot use just the date in the Query filter section for an object that is linked to field defined as Timestamp on our mainframe version of DB2. However, when I pull that object but not filter on it, the object displays the full date and time.

Now, I am not 100% sure, however when we were using DB2 connect 7.1 and went against the mainframe in the same fashion, I believe the error did not occur. Not sure if this helps you out any, but the issue might be within DB2 or DB2 connect.

dhofman (BOB member since 2006-03-17)

13

I’m seeing this same error message in this environment. What I’ve noticed is that if I select the date/timestamp as a query filter in the query panel and select the date/timestamp from a list of values the date/timestamp that I select is

9/24/2007 3:46:07 PM

When I look at the sql that is generated (and returns the above error) it generates the following…

SELECT
  ASOF_QTR_TIME_DIM.DM_CR_TMSP
FROM
  FMST.TDM_TIME_DIM  ASOF_QTR_TIME_DIM
WHERE
  ASOF_QTR_TIME_DIM.DM_CR_TMSP  =  '2007-09-24'

Any thoughts?

Eileen King :us: (BOB member since 2002-07-10)

14

Have you tried creating a pre-canned condition object which removes the timestamp for comparison/filtering purposes. It is not necessary to use the exact same object in your where clause as your select.

So, if I were to combine snippets from Eileens code:


DATE(table.date) = DATE(@Prompt('What Transaction Date are you interested in (yyyy-mm-dd)?','a',,mono,free)

For more on the DB2 date functions available, I stole a peek here: http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

digpen :us: (BOB member since 2002-08-15)

15

When I create a filter I am successful! The timestamp seems to be dropped in the sql…which is fine for what I’m trying to do…

SELECT
  ASOF_QTR_TIME_DIM.DM_CR_TMSP
FROM
  FMST.TDM_TIME_DIM  ASOF_QTR_TIME_DIM
WHERE
  ( DATE(( ASOF_QTR_TIME_DIM.DM_CR_TMSP )) = '2007-09-24'  )

What’s really odd is that I created the query and clicked on the SQL button. It won’t allow me to see the SQL til I’ve run the query…

Eileen King :us: (BOB member since 2002-07-10)

16

I believe that if you create a predefined condition this will work. Using the predefined condition (with a prompt) and entering the date without the timestamp or selecting it from the calendar returns the date with the timestamp.

I’m just still trying to figure out the cause of the error message when you just try to use it as a filter in the query pane.

Eileen King :us: (BOB member since 2002-07-10)

17

To solve this problem you should change your Windows Server Regional settings. If your location is Turkey, you should set format to Turkish, current location to Turkey from Control Panel > Region and Language folder.

Best Regards,

Ugur GUNGOR

BI Specialist
HALKBANK
Istanbul

luckdaysee :turkey: (BOB member since 2006-05-24)