This discussion is archived
4 Replies Latest reply: Jan 21, 2013 12:01 PM by rp0428 RSS

Fetch records having timing before 3 PM

Chanchal Wankhade Journeyer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points