Home » SQL & PL/SQL » SQL & PL/SQL » How to find out Date Format (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit,OS: XP Window )
How to find out Date Format [message #576581] Wed, 06 February 2013 06:03 Go to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

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 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

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 #576584 is a reply to message #576581] Wed, 06 February 2013 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
IS there way i can find out format of date


Which date is 10/11/12?

Regards
Michel
Re: How to find out Date Format [message #576585 is a reply to message #576583] Wed, 06 February 2013 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SO every time i am changing the code which is really pain.


Standardize your data.

Regards
Michel
Re: How to find out Date Format [message #576586 is a reply to message #576585] Wed, 06 February 2013 06:25 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

Unfortunately not able to standardized the data....as data is coming from client.Thats why i going this way.
Re: How to find out Date Format [message #576587 is a reply to message #576581] Wed, 06 February 2013 06:33 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
Instead of giving DD/MM/RR, always use DD/MON/YYYY... Month in thre digit to avoid not a valid month problem

Veera
Re: How to find out Date Format [message #576589 is a reply to message #576587] Wed, 06 February 2013 06:55 Go to previous messageGo to next message
cookiemonster
Messages: 11070
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 #576598 is a reply to message #576589] Wed, 06 February 2013 08:37 Go to previous messageGo to next message
chandan.rattan
Messages: 84
Registered: December 2008
Location: India
Member

I guess i have to keep begging them to give it proper standard only. If anyone come better solution i would appreciate.
Thanks for your response.
Re: How to find out Date Format [message #576599 is a reply to message #576598] Wed, 06 February 2013 08:45 Go to previous messageGo to next message
cookiemonster
Messages: 11070
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.
Re: How to find out Date Format [message #576619 is a reply to message #576599] Wed, 06 February 2013 10:53 Go to previous message
Bill B
Messages: 1115
Registered: December 2004
Senior Member
If there sending you an XLS file simply reformat the date column to you standard layout and then save it as csv.
Previous Topic: Another Pivot question
Next Topic: EXCEL LEADING ZERO
Goto Forum:
  


Current Time: Tue Oct 21 10:33:55 CDT 2014

Total time taken to generate the page: 0.52855 seconds