Help with Quering Date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Help with Quering Date

  1. #1
    Join Date
    May 2008
    Posts
    34

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    May 2008
    Posts
    34
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    May 2008
    Posts
    34

    Angry

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

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,440

    Cool

    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

  7. #7
    Join Date
    May 2008
    Posts
    34

    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

  8. #8
    Join Date
    May 2008
    Posts
    34
    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,....

  9. #9
    Join Date
    May 2008
    Posts
    34
    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
  •  



Click Here to Expand Forum to Full Width