Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2014
    Posts
    44

    Unanswered: Showing table data according to specific date range

    Hi all,

    I am new here and I want to know how to pull the table info ranging from xx-xxx-xxxx date to xx-xxx-xxxx date?
    I am designing a progress info manufacturing and I need to be able to show them if users want to monitor the daily data changes. How do i accomplish it in Oracle.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So is this a php problem
    An oracle problem
    Or something else

    On the face of it it sounds like its an Oracle problem
    The answer is going to be in the <where> clause. EG:-
    Code:
    SELECT my, column, list FROM mytable
    WHERE mydatecolumn > 2014/01/01 AND mydatecolumn < 2014/02/01
    An alternative would be to use the <between> construct
    Code:
    SELECT my, column, list FROM mytable
    WHERE mydatecolumn BETWEEN 2014/01/01 AND  2014/01/31
    however dates are one of the maion areas where various db verndors tend to vary from ISO SQL, so you will need to verufy you are using a dtae syntax and expression appropriate for Oracle

    once you have sussed that you can then substitute the limits using an appropriate PHP function

    PHP Code:
    $strSQL "SELECT my, column, list FROM mytable
    WHERE mydatecolumn BETWEEN" 
    $LowerLimt " AND " $UpperLimit 
    if you do use BETWEEN then the lower limit MUST be first and the upper limit MUST be last
    Last edited by healdem; 03-12-14 at 05:54.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2014
    Posts
    44

    Thanks

    Thanks a lot.
    Your help is greatly appreciated

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Adding a bit to healdem's post, it is good practice to convert the parameters supplied in the where condition to the correct type, therefore the oracle version would look like:
    Code:
    SELECT My, Column, List
      FROM Mytable
     WHERE Mydatecolumn 
        BETWEEN TO_DATE ( '2014/01/01', 'YYYY/MM/DD') 
            AND TO_DATE ( '2014/01/31', 'YYYY/MM/DD');
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2014
    Posts
    44

    Timestamp record

    Thank you so much. You such a big help.
    Another question, How do I monitor the daily data changes ?
    For example,

    Lets say in fabrication progress, on 12/3/2013 One variable is 10
    and on the next day a user made changes to that data into 12 because the quantity has increased.

    I want to show the daily record and the increments with PHP

    Thanks a lot

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Quote Originally Posted by chrishutagalung View Post
    . . .
    Another question, How do I monitor the daily data changes ?
    For example,
    . . .
    You would need to query some table that records the daily transactions.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Mar 2014
    Posts
    44
    So you mean that I need to make another table to record the daily transaction ??

  8. #8
    Join Date
    Mar 2014
    Posts
    44
    Quote Originally Posted by LKBrwn_DBA View Post
    Adding a bit to healdem's post, it is good practice to convert the parameters supplied in the where condition to the correct type, therefore the oracle version would look like:
    Code:
    SELECT My, Column, List
      FROM Mytable
     WHERE Mydatecolumn 
        BETWEEN TO_DATE ( '2014/01/01', 'YYYY/MM/DD') 
            AND TO_DATE ( '2014/01/31', 'YYYY/MM/DD');
    Thanks for the suggestion
    I will keep that in mind

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Another way would be to run oracle auditing against your table. It would keep track of all data changes if you audit updating.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •