This is why I use decode to change the zeros to null. My sql works except it
is returning the valid dates in the wrong format. Like I said, the syntax
here might be wrong since no database is accessible for me right now to test
run my sql. Anyway, I will wait until tomorrow to get the right syntax and
post it again. Thanks for your help.
On Thu, Aug 11, 2011 at 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
>>
>
> --
> 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
