This problem is resolved.
On loading into the actual table with the DATE type column resolves
the format issue.
Thanks.
On Aug 12, 9:19 am, JK <jyka...@gmail.com> wrote:
> I am just using SYSDATE as an example only since SYSDATE can never be
> '000000'. In my case I have the following syntax to retrieve from a
> table:
>
> SQL> select decode(bill_begin_date,'000000',null,TO_DATE
> (bill_begin_date, 'YYYYMM')) from TEMP_DETERMINATION;
>
> DECODE(BI
> ---------
>
> 01-JUL-10
> 01-AUG-10
>
> DECODE(BI
> ---------
>
> 01-AUG-10
> 01-SEP-10
>
> On Aug 11, 9:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
>
>
>
> > If you are getting '000000' then your example is not showing what you are
> > actually doing.
> > you can not make a DATE have a value of zeros.
>
> > SQL> select to_date('20110809','yyyymmdd') from dual
>
> > TO_DATE('20110809','YYYYMMDD')
> > ------------------------------
> > 09-AUG-11
> > 1 row selected.
>
> > SQL> select to_date('00000000','yyyymmdd') from dual
> > select to_date('00000000','yyyymmdd') from dual
> > *
> > Error at line 1
> > ORA-01843: not a valid month
>
> > On Thu, Aug 11, 2011 at 6:05 PM, Jyothi Kavasseri <jyka...@gmail.com> wrote:
> > > Unfortunately, we get '000000' at times.
>
> > > On Thu, Aug 11, 2011 at 9:03 PM, Michael Moore
> > > <michaeljmo...@gmail.com>wrote:
>
> > >> since it will never be '000000' for any DATE, you can simply do...
>
> > >> SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY') FROM DUAL;
> > >> Mike
>
> > >> On Thu, Aug 11, 2011 at 5:45 PM, Jyothi Kavasseri
> > >> <jyka...@gmail.com>wrote:
>
> > >>> The decode always returns the date format in DD-MMM-YY format whereas I
> > >>> want it to return in MM/DD/YYYY format.
>
> > >>> select
> > >>> decode(to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'),'000000',null,to_date(to_char(SYSDATE,'YYYYMM'),'YYYYMM'))
> > >>> from dual.
>
> > >>> As of now, when I type this I do not have a proper SQL editor to test
> > >>> the
> > >>> syntax. Hence it may not be correct. However, I could run the decode
> > >>> query
> > >>> in TOAD at my work and that is when I saw it is returning in DD-MMM-YY
> > >>> format. I hope you got some idea as to what my requirement is. Looking
> > >>> forward to your help.
>
> > >>> Thanks in advance.
>
> > >>> --
> > >>> You received this message because you are subscribed to the Google
> > >>> Groups "Oracle PL/SQL" group.
> > >>> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > >>> To unsubscribe from this group, send email to
> > >>> oracle-plsql-unsubscr...@googlegroups.com
> > >>> For more options, visit this group at
> > >>>http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> > >> --
> > >> You received this message because you are subscribed to the Google
> > >> Groups "Oracle PL/SQL" group.
> > >> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > >> To unsubscribe from this group, send email to
> > >> oracle-plsql-unsubscr...@googlegroups.com
> > >> For more options, visit this group at
> > >>http://groups.google.com/group/Oracle-PLSQL?hl=en
>
> > > --
> > > You received this message because you are subscribed to the Google
> > > Groups "Oracle PL/SQL" group.
> > > To post to this group, send email to Oracle-PLSQL@googlegroups.com
> > > To unsubscribe from this group, send email to
> > > oracle-plsql-unsubscr...@googlegroups.com
> > > For more options, visit this group at
> > >http://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
