4 Replies Latest reply: Jan 21, 2013 2:01 PM by rp0428 RSS

    Fetch records having timing before 3 PM

    Chanchal Wankhade
      Hellow All,

      We are using Oracle 10G R2 on windows.

      I have a requirment that i need to fetch the records which are having cut off time before 3 pm.
      query should not fetch records after 3 pm.
      Please help how we can achive this.

      For testing purpose I have records like below
      SQL> select empno,emptype,to_char(jdate,'dd-mm-yyyy hh:mi:ss')jdate from test;
      
           EMPNO EMPTYPE              JDATE
      ---------- -------------------- -------------------
               6 back office          24-12-0012 03:00:00
               1 HR                   01-01-2012 03:01:00
               2 Admin                01-12-2011 03:20:00
               3 MARKETING            01-01-2012 03:00:00
               4 FACILITY             12-02-2012 03:00:00
               8 IT                   10-06-2010 03:14:00
               9 Finance              07-11-2012 03:10:00
              10 "100HR"              07-11-2012 03:31:00
      
      8 rows selected.
      In the above case records having time more than 3 pm should not fetch.
        • 1. Re: Fetch records having timing before 3 PM
          NSK2KSN
          check if the below query is giving the expected results
          select empno,emptype,to_char(jdate,'dd-mm-yyyy hh24:mi:ss')jdate from test
          where to_char(jdate,'hh24:mi:ss') <= '15:00:00'
          • 2. Re: Fetch records having timing before 3 PM
            NSK2KSN
            I have tried below query, it is giving me
            /* Formatted on 1/21/2013 3:25:20 PM (QP5 v5.215.12089.38647) */
            WITH test
                 AS (SELECT 6 empno,
                            'back office' emptype,
                            TO_DATE ('24-12-2012 15:00:00', 'dd-mm-yyyy hh24:mi:ss')
                               jdate
                       FROM DUAL
                     UNION ALL
                     SELECT 6 empno,
                            'back office' emptype,
                            TO_DATE ('24-12-2012 15:40:00', 'dd-mm-yyyy hh24:mi:ss')
                               jdate
                       FROM DUAL
                     UNION ALL
                     SELECT 1,
                            'HR',
                            TO_DATE ('01-01-2012 03:01:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 2,
                            'Admin',
                            TO_DATE ('01-12-2011 03:20:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 3,
                            'MARKETING',
                            TO_DATE ('01-01-2012 03:00:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 4,
                            'FACILITY',
                            TO_DATE ('12-02-2012 03:00:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 8,
                            'IT',
                            TO_DATE ('10-06-2010 03:14:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 9,
                            'Finance',
                            TO_DATE ('07-11-2012 03:10:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL
                     UNION ALL
                     SELECT 10,
                            '100HR',
                            TO_DATE ('07-11-2012 03:31:00', 'dd-mm-yyyy hh24:mi:ss')
                       FROM DUAL)
            SELECT empno, emptype, TO_CHAR (jdate, 'dd-mm-yyyy hh24:mi:ss') jdate
              FROM test
             WHERE TO_CHAR (jdate, 'hh:mi:ss') <= '03:00:00'
            records for 6, 3 and 4 empno's check if this is your requirement
            • 3. Re: Fetch records having timing before 3 PM
              jeneesh
              Chanchal Wankhade wrote:
              Hellow All,

              We are using Oracle 10G R2 on windows.

              I have a requirment that i need to fetch the records which are having cut off time before 3 pm.
              query should not fetch records after 3 pm.
              Please help how we can achive this.

              For testing purpose I have records like below
              SQL> select empno,emptype,to_char(jdate,'dd-mm-yyyy hh:mi:ss')jdate from test;
              
              EMPNO EMPTYPE              JDATE
              ---------- -------------------- -------------------
              6 back office          24-12-0012 03:00:00
              1 HR                   01-01-2012 03:01:00
              2 Admin                01-12-2011 03:20:00
              3 MARKETING            01-01-2012 03:00:00
              4 FACILITY             12-02-2012 03:00:00
              8 IT                   10-06-2010 03:14:00
              9 Finance              07-11-2012 03:10:00
              10 "100HR"              07-11-2012 03:31:00
              
              8 rows selected.
              In the above case records having time more than 3 pm should not fetch.
              In your sample data, NONE of the records are after 3PM. Some are after 3AM.

              You could filter like..
              where jdate between trunc(jdate) and trunc(jdate)+(15/24)
              • 4. Re: Fetch records having timing before 3 PM
                rp0428
                >
                I have a requirment that i need to fetch the records which are having cut off time before 3 pm.
                query should not fetch records after 3 pm.
                >
                Be aware that trying to use a column such as 'JDATE' is likely to give the wrong results if you are trying to do this on a daily basis.

                It depends on how JDATE gets populated. If you use SYSDATE in an INSERT/UPDATE query to populate the column you can miss data.

                That is because the SYSDATE value will often get determined when the query is being processed but the data won't become 'real' until the data is committed.

                So if your query begins executing before 3 pm but is not committed until after 3 pm then, depending on when you query the data your SELECT query may not see that 'before 3 pm' data since it hasn't been committed yet.

                Then if you query tomorrow for data after 3pm today but before 3pm tomorrow that data won't get queried either because the JDATE column will have a timestamp before 3 pm today even though it was committed after 3 pm. That data won't get processed at all.