Home » SQL & PL/SQL » SQL & PL/SQL » date validation (OS)
date validation [message #674994] Fri, 01 March 2019 04:31 Go to next message
suji6281
Messages: 134
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #674996 is a reply to message #674995] Fri, 01 March 2019 04:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Some examples of valid and invalid dates would also be an idea.
Re: date validation [message #674997 is a reply to message #674996] Fri, 01 March 2019 04:58 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi

My file should have always 'DD-MON-YY' format date value.
If date value is other than this format or any other special characters ($,&,*,etc..) in it, i should validate it.

Correct Date value example: 15-FEB-19
Wrong Date value example: ABC or 2$-FEB-19

I am using Oracle data base.

thank you
Re: date validation [message #674998 is a reply to message #674997] Fri, 01 March 2019 05:03 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #674999 is a reply to message #674998] Fri, 01 March 2019 05:24 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi

1) What method are you using to get the contents of the flat file into the DB? -- I am using PeopleSoft tool to read and load data into DB. But behind da is oracle SQL.
2) What do you want to happen to rows with invalid dates? -- I should write into log file.
3) What oracle version are you using? -- Oracle 12C
Re: date validation [message #675000 is a reply to message #674999] Fri, 01 March 2019 05:29 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #675001 is a reply to message #675000] Fri, 01 March 2019 05:50 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi

data type of this date column is date format field.

Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

thank you.

Re: date validation [message #675002 is a reply to message #675001] Fri, 01 March 2019 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at LOG ERRORS clause of INSERT statement.

Re: date validation [message #675003 is a reply to message #675002] Fri, 01 March 2019 06:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #675004 is a reply to message #675003] Fri, 01 March 2019 07:15 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thank 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.
Re: date validation [message #675005 is a reply to message #675004] Fri, 01 March 2019 07:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
suji6281 wrote on Fri, 01 March 2019 05:15
Thank 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.

Nobody here prevents you from doing as you desire.
Please proceed to implement your ideas.
Re: date validation [message #675006 is a reply to message #675004] Fri, 01 March 2019 07:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
suji6281 wrote on Fri, 01 March 2019 13:15
Thank 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 #675007 is a reply to message #675004] Fri, 01 March 2019 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want to write some custom message about the invalid date format and proceed with rest of data rows in the file.
Have a look at the link I posted and come back to tell us if this is what you want and if not explain why.

Re: date validation [message #683695 is a reply to message #675007] Sat, 13 February 2021 19:42 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Issue has been resolved. Thank you.
Re: date validation [message #683697 is a reply to message #683695] Sun, 14 February 2021 08:53 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
suji6281 wrote on Sat, 13 February 2021 19:42
Issue has been resolved. Thank you.
After nearly two years . . . Shocked

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

Previous Topic: Max value row and lookup value
Next Topic: How to show records on zero on hand (Job pick list)
Goto Forum:
  


Current Time: Thu Mar 28 09:18:52 CDT 2024