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: 106
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: 13894
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: 13894
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: 106
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: 13894
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: 106
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: 13894
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: 106
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: 67290
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: 13894
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: 106
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: 26730
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: 13894
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 message
Michel Cadot
Messages: 67290
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.

Previous Topic: How to denormalize multiple records into 1 row-
Next Topic: checkbox charachter
Goto Forum:
  


Current Time: Sat Aug 08 09:22:48 CDT 2020