-
Help with Quering Date
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
Last edited by NBSR; 05-07-2008 at 04:11 PM.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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 ... 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).
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Can anyone say me whats wrong with this QUERY
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,..... ....Thx to all ur guys for helping me and supporting me with ur kind knowledge...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|