Clarity

Hi, I'm new to CA PPM. As part of my work I has to pass 'Date' value as parameter. When hard coded the date value in NSQL gives no error but when passed as parameter, raises an error "ORA-01861: literal does not match format string" (The value being passed is in YYYY-MM-DD"T"HH:MI:SS format.). I tried converting the input value by TO_DATE, TO_TIMESTAMP_TZ but nothing works. Please suggest me what else should I do

Where is your NSQL?

If you're using (for example) any to_date() / to_char() methods around this construct for your date parameter, most likely in order to run the same non-NSQL version of the query in another query editor outside Clarity, then the date format expectations will be different.

Normally in Clarity NSQL portlets you do not need to use those conversion functions.

Also I would expect that YYYY-MM-DD"T"HH:MI:SS is the wrong format to use for the incoming data to an NSQL parameter, and YYYY-MM-DD HH24:MI:SS is expected instead.

You need to give us a little more information (screen-shots / bits of code) so we can see what you really mean?

(but basically, like Nick indicates, just leave dates as dates and "everything works" - it can get fiddly when you are trying to get one portlet to "call" another portlet, but I don't think you are doing that?)

If your intention is to have user-defined NSQL parameter of DATE datatype, you don't even need any conversion.  You can directly use @WHERE:PARAM:USER_DEF:DATE:YOUR_DATE_PARAM@

Hi All,

Thanks for your quick response. My issue is solved.

David Morton, Sridhar_Bhandaru @ Your suggestions really worked. When I stopped converting/casting the date param value, system accepted it, and I was able to fetch data.

Nick Darlington@ The correct date format I used was YYYY-MM-DD"T"HH24:MI:SS, as I was getting the date value with 'T' in between. Your previous post related to this "XOG error message [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01861 " helped me getting the things.