Hi All,
When i try to execute the below query in sql developer/Toad it's fecthing the required results.
DATE1 & DATE2 is date datatype in oracle
SELECT
nvl(abs(to_date(DATE2,'dd/mm/yyyy') - to_date(DATE1,'dd/mm/yyyy')),0) as DATEDIFF,
FROM TABLE;
But, when i try the same in Oracle Connector in Datastage it throws me the below error message.
Error code: 1858, Error message: ORA-01858: a non-numeric character was found where a numeric was expected.
Kindly let me know your thoughts on the same.
Thanks.
Error message: ORA-01858: a non-numeric character was found
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Sure, that's one way to "fix" the issue. All that shows is that you did indeed have "non-numeric" data in your output somewhere. Keep in mind all it takes is one record to cause that and it's not necessarily something you'll see in Toad. You'll see it there if the issue happens within a join but the output data is not typed in Toad whereas it is in DataStage and that's why you can see it in one tool when the problem is only in what you've selected but not the other.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Error message: ORA-01858: a non-numeric character was fo
satheesh_color wrote:Hi All,
When i try to execute the below query in sql developer/Toad it's fecthing the required results.
DATE1 & DATE2 is date datatype in oracle
SELECT
nvl(abs(to_date(DATE2,'dd/mm/yyyy') - to_date(DATE1,'dd/mm/yyyy')),0) as DATEDIFF,
FROM TABLE;
But, when i try the same in Oracle Connector in Datastage it throws me the below error message.
Error code: 1858, Error message: ORA-01858: a non-numeric character was found where a numeric was expected.
Kindly let me know your thoughts on the same.
Thanks.
When date1 and date2 are already date data types, i dont understand why are you trying to convert to date again?
You can simply write abs(date1-date2).