date validation [message #674994] |
Fri, 01 March 2019 04:31 |
|
suji6281
Messages: 135 Registered: September 2014
|
Senior Member |
|
|
Hi team,
Could you please help me how to validate date.
Assume, input file has the date column and its has value other than date. Then how would validate it.
Example: File has date column as some special characters other than date format (ex: 15-FEB-19)
Please help me.
Thank you.
Regards
Sekhar
|
|
|
Re: date validation [message #674995 is a reply to message #674994] |
Fri, 01 March 2019 04:43 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That depends:
1) What method are you using to get the contents of the flat file into the DB?
2) What do you want to happen to rows with invalid dates?
3) What oracle version are you using?
|
|
|
|
|
Re: date validation [message #674998 is a reply to message #674997] |
Fri, 01 March 2019 05:03 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I assumed you were using oracle, otherwise you're in completely the wrong place.
That doesn't answer any of my three questions though.
|
|
|
|
Re: date validation [message #675000 is a reply to message #674999] |
Fri, 01 March 2019 05:29 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
What is the data type of the column you're trying to put this date data into?
What is the exact oracle version? run select * from v$version if you're not sure.
|
|
|
|
|
Re: date validation [message #675003 is a reply to message #675002] |
Fri, 01 March 2019 06:06 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
So presumably the invalid dates aren't getting loaded right now.
Understand - we know nothing about the peoplesoft tool you're using.
What does happen right now? Is all the data getting rejected because of some invalid dates?
If so, look at log errors as mentioned by Michel above.
If not, what is happening?
Because I would have thought that any half decent loading tool would handle this already.
If the data is in a text file then maybe you should try using sqlloader instead - that'll log all rows that can't be loaded to a file.
|
|
|
|
|
Re: date validation [message #675006 is a reply to message #675004] |
Fri, 01 March 2019 07:49 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
suji6281 wrote on Fri, 01 March 2019 13:15Thank you Michel and cookiemonster.
When ever i received invalid date format then my program is rejected due to some invalid date value. and stop processing other rows of file.
But now, I want to write some custom message about the invalid date format and proceed with rest of data rows in the file.
thank you.
Again - we know nothing about Peoplesoft.
We don't know what it's doing to read the data and put in the database.
Without knowing that we can't make anything other than very generic suggestions - LOG ERRORS and use sqlloader.
If you can't use log errors and don't want to use sqlloader then you are either going to have to explain to us how Peoplesoft interacts with the DB and what you are / aren't allowed to change or go find a Peoplesoft forum.
|
|
|
|
|
Re: date validation [message #683697 is a reply to message #683695] |
Sun, 14 February 2021 08:53 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
suji6281 wrote on Sat, 13 February 2021 19:42Issue has been resolved. Thank you.
After nearly two years . . .
And, I might add, that two years ago you said:
Quote:Correct Date value example: 15-FEB-19
Uh, no. The correct date value example would be 15-FEB-2019.
It's been more than 20 years since I and armies of my colleagues were busting our butts to resolve the Y2k problem before midnight, 31-Dec-1999. Now you want to recreate the same problem.
[Updated on: Sun, 14 February 2021 13:00] Report message to a moderator
|
|
|