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 number01722. 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:)
You code should look like this;
SELECT trunc(NVL(@WHERE:PARAM:USER_DEF:DATE:startdate@, sysdate), 'DY') + 1 cur_monday FROM dual
SELECT trunc(NVL(:param_startdate, sysdate), 'DY') + 1 cur_monday FROM dual
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.
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
thanks. i am getting the result.