Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: SQL Query help(urgent)

    Hi.. Need some help from some experts here..

    I have this query that currently selects data from the oracle database. I need some help with the SQL statement to retrieve records that are 5 hours recent.

    The type in the database is datetime and the format is as follows :

    6/1/2003 10:24:55 AM

    I'm unable to change any of the database structure. Is there any SQL command that selects the records, based on that format to take those 5 hours recent?

    The current SQL statement is as follows :

    Code:
    SELECT P.LICENSE_PLATE_NBR, P.PM_ARRIVE_DTTM, P.PM_EXIT_DTTM, P.EXIT_LANE_TYPE,  P.STATUS, P.HDLG_IND, C.CNTR_NBR, Y.CO_NM, X.PHONE1_NBR
    
    FROM PREGATE P, CNTR C, CUST_CONTACT X, COMPANY_CODE Y 
    
    WHERE P.CNTR_SEQ_NBR = C.CNTR_SEQ_NBR 
    AND P.HAULIER_CD = X.CUST_CD
    AND P.HAULIER_CD = Y.CO_CD
    AND P.STATUS IN ('A','E','P')
    AND C.PURP_CD <> 'EX'
    AND C.STATUS = 'F'
    
    ORDER BY P.PM_ARRIVE_DTTM
    P.PM_ARRIVE_DTTM is the column where the date is stored(Format : 6/1/2003 10:24:55 AM)

    Any help is appreciated. Thank you.

  2. #2
    Join Date
    Oct 2003
    Posts
    5

    Re: SQL Query help(urgent)

    I have this query that currently selects data from the oracle database. I need some help with the SQL statement to retrieve records that are 5 hours recent.

    The type in the database is datetime and the format is as follows :

    6/1/2003 10:24:55 AM
    The current SQL statement is as follows :

    SELECT P.LICENSE_PLATE_NBR, P.PM_ARRIVE_DTTM, P.PM_EXIT_DTTM, P.EXIT_LANE_TYPE, P.STATUS, P.HDLG_IND, C.CNTR_NBR, Y.CO_NM, X.PHONE1_NBR
    FROM PREGATE P, CNTR C, CUST_CONTACT X, COMPANY_CODE Y
    WHERE P.CNTR_SEQ_NBR = C.CNTR_SEQ_NBR
    AND P.HAULIER_CD = X.CUST_CD
    AND P.HAULIER_CD = Y.CO_CD
    AND P.STATUS IN ('A','E','P')
    AND C.PURP_CD <> 'EX'
    AND C.STATUS = 'F' AND
    P.PM_ARRIVE_DTTM>=SYSDATE-(1/6*4)*5
    ORDER BY P.PM_ARRIVE_DTTM

    Try this will give records last recent five hours. w.r.t P.PM_ARRIVE_DTTM

  3. #3
    Join Date
    Nov 2003
    Posts
    16
    I'll try that and see if it works. Appreciate the help

  4. #4
    Join Date
    Nov 2003
    Posts
    16
    Thanks a mil' dude. Works perfect. So I assume that if I wish to set the time to 24 hours the query statement would be

    Code:
    P.PM_ARRIVE_DTTM >= SYSDATE - (1 / 6 * 4) * 24

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    SELECT TO_CHAR(P.PM_ARRIVE_DTTM ,'DD/MM/YYYY HH24:MIS AM')
    and P.PM_ARRIVE_DTTM>= SYSDATE - (1 / 6 * 4) *5

    sysdate-1/6 gives Last 4 hours so divide by 4 make it one hour and multiply by 5

Posting Permissions

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