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

    Unanswered: records retrieved from wrong year

    Hi all...

    I have this query that retrieves stuff I need for my Crystal Report. The thing that bugs me is that it also takes records from the previous years. Anyone knows a way to get around this?

    Btw, the dates are entered by the user

    Code:
    SELECT VES.SHPG_SVC_CD, BERTHING.ATB_DTTM, BIL.CNTR_OPR_CD, BIL.TARIFF_CD, BIL.NBR_CNTR 
    FROM TOPS.BILL_ITEM BIL,TOPS.BERTHING BERTHING, TOPS.VESSEL_CALL VES 
    WHERE ((VES.VV_CD=BERTHING.VV_CD) AND (BIL.VV_CD=VES.VV_CD))
    AND (TO_CHAR(BERTHING.ATB_DTTM, 'MM/DD/YYYY') BETWEEN '{?StartDate}' AND '{?EndDate}')
    AND (BIL.TARIFF_TYPE = 'P') 
    AND (BIL.TARIFF_MAIN_CAT_CD = 'SV') 
    AND (BIL.TOTAL_ITEM_AMT > 0) 
    AND (BIL.POST_IND <> 'E')
    AND (SUBSTR(BIL.TARIFF_CD,7,2)='RF' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='RH' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='RH' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='MR' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='HR' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='FT')
    AND SUBSTR(BIL.TARIFF_CD,4,2)='GL'
    AND SUBSTR(BIL.TARIFF_CD,9,2)='IT'
    ORDER BY 
    VES.VSL_NM,
    VES.OUT_VOY_NBR,
    BERTHING.ATB_DTTM,
    BIL.CUST_CD,
    BIL.SLOT_OPR_CD,
    BIL.CNTR_OPR_CD,
    BIL.TARIFF_SUB_CAT_CD,
    BIL.ITEM_NBR
    {?StartDate} and {?EndDate} will be replaced by the date the user enters. Eg, 11/01/2003 and 11/30/2003. Problem is, I have records from the year 2002 from that query. Anyone can help? Thanks

  2. #2
    Join Date
    Sep 2003
    Posts
    27
    I think it is because you are comparing date values as strings.

    If you convert the date '12-JAN-2002' to a string of '11/20/2002' using TO_CHAR.
    As a STRING, this value will fall between: '11/01/2003' and '11/30/2003'.
    I think it compares ASCII equivalents.


    You might need to convert your values to dates before you compare them.

    e.g.
    ...AND BERTHING.ATB_DTTM BETWEEN TO_DATE('{?StartDate}') AND TO_DATE('{?EndDate}'))

    Cheers,
    Tim

  3. #3
    Join Date
    Sep 2003
    Posts
    27
    Apologies.

    For: '12-JAN-2002' to a string of '11/20/2002'

    Read: '20-NOV-2002' to a string of '11/20/2002'

    That makes more sense.

  4. #4
    Join Date
    Nov 2003
    Posts
    16
    I'll try that. Thanks Tim

Posting Permissions

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