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