Printable View
Actually i havnt yet written the code as i am not getting the logic...
what i want to do is ... my procedure will be having an input from front end of date as 2007 now this date have to be queried on data in a cursor and give me output form 1-1-2007 to 12-30-2007
it should get me all the data of that year,... how is this possible,... i have no idea,... can anyone help me out with this plzzzz
NBSR
You alredy know how a LOOP works, may be if you research DATE functions something would pop up in your mind.
I am really tring... but...now i put 2007 value in a variable P and in query what should i use like
where to_char(date, YYYY)=p
will this do...
or what should i do...i am not getting any idea..
i know i cant use like because p is a variable with value that have to be matched...
Mmmm ... now i am really out of ideas...
Can't help you without doing your job and, I'm not doing your job.
This is a logic issue, not an Oracle issue.
You have to design the logic you are going to use to solve the problem, then you look at what tools Oracle offers you to implement your solution.
Think, research. If you can't come out with an idea google it.
I am trying... i know the logic but i am not getting the right statement or function... i have searched a lot on dates yesturday night but couldnt find much... havnt slept at all :o ... still i will not take my defeat...i will work more more and more hard....OK!!:)
Here is a hint:
Query your table where the year from the date column in the table is equal to input (2007). ;)
SQL> select certificate_nbr,
2 part171,
3 inactive_ind,
4 hts_cde,
5 originating_ind,
6 iso_country_cde,
7 basis_cde,
8 producer_cde,
9 cert_cde,
10 regional_value_content_cde,
11 part_nbr,
12 effective_date,
13 effective_from_date,
14 effective_to_date,
15 transaction_date
16 from SAP_CERTIFICATES_EXTRACT_VIEW
17 where org_id= '0048' AND
18 customer_nbr_base= '00000065' AND
19 customer_nbr_sufx = '11' AND
20 to_char(EFFECTIVE_FROM_DATE , 'YY')='04' AND
21 to_char(EFFECTIVE_TO_DATE, 'yy')='04';
Error
to_char(EFFECTIVE_FROM_DATE , 'YY')='04' AND
*
ERROR at line 20:
ORA-01722: invalid number
all spelling are correct... to_char is correct..... date formate is correct.... bit why there is an error is the question...
though i tried it on other table in same way it worked
select to_char(effective_dte, 'mm-dd-yy'),
to_char(expiration_dte, 'mm-dd-yy'), org_id
from certificates
where to_char(effective_dte, 'yy')='04'
what can possible be wrong...??
-NBSR
I got the idea before i saw ur hint... i took it as a challenge bit got stuck with the error and unable to slove it... give me some hints here,....
Got it.... God i am learing a lot... i am becoming smart,.....:D ....Thx to all ur guys for helping me and supporting me with ur kind knowledge...