| How to find out Date Format [message #576581] |
Wed, 06 February 2013 06:03  |
|
|
hi, I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.
Now every time i have to open my code and change it there ...to make it as oracle date format.
IS there way i can find out format of date and based on format i can do operations with getting errors every time.
Kindly note that i m storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().
Thanks
|
|
|
|
| Re: How to find out Date Format [message #576583 is a reply to message #576581] |
Wed, 06 February 2013 06:10  |
|
|
For Ex. Some cases i used to get the date as in form 07-DEC-1998 (DD-MON-RRRR) then to convert to date as
SELECT TO_DATE(M_DATE,'DD-MON-RRRR') FROM DUAL
in some cases i got the date as format 11/22/84(MM/DD/RR) which converting into date throw the error.Not valid Month.This date will work if change my code as
SELECT TO_DATE(M_DATE,'MM/DD/RRRR') FROM DUAL
SO every time i am changing the code which is really pain.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to find out Date Format [message #576589 is a reply to message #576587] |
Wed, 06 February 2013 06:55 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've misunderstood the OPs problem.
@chandan.rattan - the only safe solution is to standardize the data you recieve. So I suggest you explain to the client that if they don't standardize the data sooner or later they will get wrong data in the DB.
Just use Michel's example above - what date is that?
|
|
|
|
|
|
| Re: How to find out Date Format [message #576599 is a reply to message #576598] |
Wed, 06 February 2013 08:45 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Don't beg. Explain to them, with examples, exactly how their data will be corrupted if they don't standardize their data, or at the very least specify which format they're using in the spreadsheet they send you.
If they aren't clear what their dates are then sooner or later the wrong date will end up in the DB - and that'll be their fault.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
