I can not reproduce your problem. It work fine for me. What are you doing
that is different than what I am doing?
SELECT * FROM employee;
DECLARE
dt NUMBER;
BEGIN
SELECT COUNT( * )
INTO dt
FROM employee
WHERE TO_DATE( year_month_day, 'YYYYMMDD' ) = TO_DATE( '20000603',
'YYYYMMDD' );
DBMS_OUTPUT.put_line( TO_CHAR( dt ) );
END;
script output -------
YEAR_MONTH_DAY
--------------
20000603
1 row selected.
PL/SQL procedure successfully completed.
dbms output ---------
1
On Tue, Aug 24, 2010 at 1:48 PM, swaroop gowda <swaroop.t...@gmail.com>wrote:
> I defined dt in pl/sql declaration part and the data type was date for 2nd
> quesry and for 1st query it was number.
>
>
> On Tue, Aug 24, 2010 at 3:44 PM, Michael Moore <michaeljmo...@gmail.com>wrote:
>
>> Where do you define DT ?
>>
>>
>> On Tue, Aug 24, 2010 at 1:00 PM, swaroop gowda <swaroop.t...@gmail.com>wrote:
>>
>>> Mike,
>>>
>>> I checked the data as well. There is no bad data.
>>>
>>> On Tue, Aug 24, 2010 at 2:51 PM, Michael Moore <
>>> michaeljmo...@gmail.com> wrote:
>>>
>>>> You have bad date data in employee.year_month_day. Check the dates in
>>>> that table and you will find a bad one.
>>>>
>>>> Mike
>>>>
>>>>
>>>> On Tue, Aug 24, 2010 at 12:38 PM, swaroop gowda <
>>>> swaroop.t...@gmail.com> wrote:
>>>>
>>>>> I am trying to get count from one of the table by using date but
>>>>> date value is stored as string like 20000603 it is VARCHAR2 field
>>>>>
>>>>> Begin
>>>>> SELECT
>>>>> count(*) into dt FROM employee where to_date(year_month_day,'YYYYMMDD')
>>>>> = to_date('20000603','YYYYMMDD');
>>>>> End;
>>>>> It is throwing an error saying not a valid month when I execute this in
>>>>> Toad.
>>>>> Same thing if I execute like below It works fine.
>>>>>
>>>>> select next_day(to_date('20000603','YYYYMMDD')-7,'SUNDAY') into dt from
>>>>>
>>>>> employee where rownum = 1;
>>>>>
>>>>> Please let me know if any one knows why it is giving an error.
>>>>>
>>>>> --
>>>>> Thanks & Regards
>>>>> Swaroop Thailuru Swamy
>>>>>
>>>>> --
>>>>> 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
>>>
>>>
>>>
>>>
>>> --
>>> Thanks & Regards
>>> Swaroop Thailuru Swamy
>>>
>>> --
>>> 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
>>
>
>
>
> --
> Thanks & Regards
> Swaroop Thailuru Swamy
>
> --
> 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
