param_startdate is not taking any values

[Legacy User]

Hello Everyone

i am working on some portlet issue, where the background query for this portlet is having the code like this SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual.

:param_startdate is not taking any values given in the format like this: 02062017, 06022017, 06-FEB-2017, 020617, 060217and 06-FEB-17. this giving me an error.

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

 

checked this ora-01722 in google, its saying :The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

not sure where i am missing any value/type/format.

waiting for your wonderful solutions:)

 

Thanks

Daya

[David Morton]

You code should look like this;

 

SELECT trunc(NVL(@WHERE:PARAM:USER_DEF:DATE:startdate@, sysdate), 'DY') + 1 cur_monday FROM dual

not

SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual

[Legacy User]

HI Dave,

 

Sorry i did not tell you that i am running this query in Oracle SQL Developer.

and the query its working fine in the clarity query/portlet section. to check the result i took out by clicking the preview and stuck at this line to get the result.

SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual

 

Thanks

Daya

[David Morton]

So that will depend on whether you have set a date format in your SQL*Developer or not (when it converts your string input to a date datatype)

 

But if all you are doing is "debugging" then perhaps do this sort of thing;

SELECT trunc(NVL(to_date(:param_startdate,'dd/mm/yyyy'), sysdate), 'DY') + 1 cur_monday FROM dual

then you know how the string to date conversion is going to work

[Legacy User]

HI Dave

 

thanks. i am getting the result.

Thanks Again

Daya