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 -
--
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
