Hi Jyothi,
Yes, a variable, (or table column) with a datatype of DATE can not be
zeros.
The key concept here is DATATYPE, not 'type', not 'data type', but
'datatype'.
These are all different things.
regards,
Mike
On Fri, Aug 12, 2011 at 7:27 AM, JK <jyka...@gmail.com> wrote:
> 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
>
--
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
