Another DB2 Timestamp problem

[DrSmyth]

Hi, following advise from this forum I've always used the date() function in my universe if i want to use a date as a parameter. And this has consistantly worked.

However, I've had a request to make a report whereby people can use the date and time (full timestamp) as a condition for reporting.

I've tried using the Timestamp() function but i'm getting the old problem:

Exception: DBD, [IBM][CLI Driver][DB2] SQL0180N  The syntax of the string representation of a datetime value is incorrec

was wondering if anybody had come across a solution to this problem. I'm using BO 6.5.1 and DB2 v8.1.7

Here's my sql if it;'s relevant:

SELECT
  LLPSC01S.PSC_PSC_TRAN_V.CR_AMT,
  LLPSC01S.PSC_PSC_TRAN_V.DR_AMT,
  timestamp(LLPSC01S.PSC_PSC_TRAN_V.POST_DT),
  LLPSC01S.PSC_PSC_TRAN_V.NARR,
  LLPSC01S.PSC_PSC_TRAN_V.LST_MNTN_USER_ID,
  LLPSC01S.PSC_PSC_TRAN_V.RVSL_IND
FROM
  LLPSC01S.PSC_PSC_TRAN_V,
  LLPSC01S.PSC_PSC_PYMTMETH_V
WHERE
  ( LLPSC01S.PSC_PSC_PYMTMETH_V.PYMT_METH=LLPSC01S.PSC_PSC_TRAN_V.PYMT_METH  )
  AND  (
  LLPSC01S.PSC_PSC_TRAN_V.ACCT_COD  LIKE  'GLB#BANK#NO1'
  AND  LLPSC01S.PSC_PSC_TRAN_V.POST_IND  =  'Y'
  AND  LLPSC01S.PSC_PSC_TRAN_V.TRAN_TYP  NOT IN  ('BCI', 'BIA', 'BIC')
  AND  LLPSC01S.PSC_PSC_PYMTMETH_V.PYMT_METH  IS NOT NULL  
  AND  timestamp(LLPSC01S.PSC_PSC_TRAN_V.POST_DT)  >=  @variable('Start')
  )

 

[blom0344]

TIMESTAMP in DB2 is quite specific.

It can handle a few variations where syntax is concerned.

The easiest would be:

'YYYYMMDDHHMMSS'

like:

'20060925192150'

or:

'YYYYMMDD' , 'HHMMSS'

You could define a prompt that specifies how the input should look like.

And also in this case check with Graeme Birchall's SQL Cookbooks!

Ties Blom