Home » SQL & PL/SQL » SQL & PL/SQL » Urgent:Please help me out in Date Conversion
Urgent:Please help me out in Date Conversion [message #187564] Mon, 14 August 2006 08:30 Go to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
Hi All,
Please find below piece of code

PFCISUOBD@PFCISDB>>> Declare
2 tPromStartDate Date;
3 BEGIN
4 Select To_Date(OtherInfo5,'dd-mon-yyyy') Into tPromStartDate
5 From FundAddInfoTbl
6 Where RuleEffectiveDate='26-May-2006'
7 And Fundid='F8CG'
8 And AuthType = 'A';
9 end;
10 /
Declare
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 4

The Column OtherInfo5 is of Varchar and will be storing the date according the registry setting.

Eg: if my registry settings for short & long dates are
dd/mm/yyyy
then Otherinfo5 will store the value as 01/01/2006. But when i do above conversion displaying above error message.

Could you please any of you help me out in sorting the same.

Please be noted that i will be storing values for Otherinfo5 accroding to the registry settings.

Thanks & Regards,
Anil
Re: Urgent:Please help me out in Date Conversion [message #187566 is a reply to message #187564] Mon, 14 August 2006 08:41 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If otherinfo5 is a date stored as a varchar2 you can use the to_date, if it is already a date you don't need the to_date. Make sure that the information in otherinfo5 represents a date of format dd-mon-yyyy.

If ruleeffectivedate is a date, don't compare it to a varchar2 but use a to_date instead.

Basically, we need the table description.

MHE
Re: Urgent:Please help me out in Date Conversion [message #187579 is a reply to message #187564] Mon, 14 August 2006 09:34 Go to previous messageGo to next message
gconner1997
Messages: 10
Registered: July 2006
Location: New York
Junior Member
Your statement:

''Eg: if my registry settings for short & long dates are
dd/mm/yyyy
then Otherinfo5 will store the value as 01/01/2006. But when i do above conversion displaying above error message.''

I'm guessing you think that the line:
''Select To_Date(OtherInfo5,'dd-mon-yyyy') Into tPromStartDate''
will convert OtherInfo5 from a format of dd/mm/yyyy into dd-mon-yyyy. That would not be the case. You have to tell the function what format you are passing into it, not what you want. ( the to_char() gives you what you want)

So if your date is actually 01/01/2006, the above line needs to be:
''Select To_Date(OtherInfo5,'dd/mm/yyyy') Into tPromStartDate''
to avoid the ORA-01843: not a valid month,

Also dd/mon/yyyy will return the same error, If you pass a number you need to use dd/mm/yyyy.


(I spent many frustrating hours trying to pass dates from VB6 to Oracle. I found the best is always pass the date in 'dd/mm'yyyy' format as a string and let my functions and packages do the to_date() for me)
Re: Urgent:Please help me out in Date Conversion [message #187694 is a reply to message #187579] Mon, 14 August 2006 23:22 Go to previous messageGo to next message
AnilOrafaq
Messages: 12
Registered: May 2006
Junior Member
Thanks for your prompt replies.

I will agree to do conversion with dd/mm/yyyy if i'm storing the otherinfo5 in dd/mm/yyyy format.

But what if i don't know in which format i'm storing the values.
In that case i cannot always use either dd-mon-yyyy or dd/mm/yyyy.

Thanks & Regards,
Anil
Re: Urgent:Please help me out in Date Conversion [message #187726 is a reply to message #187694] Tue, 15 August 2006 03:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

But what if i don't know in which format i'm storing the values.
In that case i cannot always use either dd-mon-yyyy or dd/mm/yyyy.



If you're talking about a column of type DATE then this isn't a problem - they're just stored as DATEs, with no inherrent format. They only aquire a format when you convert the to a string.

If you're talking about a date stored in a varchar, then I'm afraid its your job to know what format the data in the column is in.
Previous Topic: Bulk Longest Match Query
Next Topic: ORDER BY - special edition.....
Goto Forum:
  


Current Time: Sun Dec 04 08:19:20 CST 2016

Total time taken to generate the page: 0.15763 seconds