Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Query not working with between condition

    I have query like this

    Select NVL(SUM(TRANS_QTY),0) FROM IC_TRAN_PND where ITEM_ID IN (130,131) and line_type=1 AND COMPLETED_IND=1 AND
    to_date(TRANS_DATE,'DD-MON-YY') Between to_date('01-JAN-12','DD-MON-YY') and to_date(('04-JAN-12'),'DD-MON-YY')

    this query not returning the total sum of records between the two date ranges.

    My object is to get sum of a column between two date ranges. and these two dates are variable. User can change the it by aspx page

    Regards

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT Nvl(SUM(TRANS_QTY), 0) 
    FROM   IC_TRAN_PND 
    WHERE  ITEM_ID IN ( 130, 131 ) 
           AND line_type = 1 
           AND COMPLETED_IND = 1 
           AND To_date(TRANS_DATE, 'DD-MON-YY') BETWEEN 
               To_date('01-JAN-12', 'DD-MON-YY') AND 
               To_date(( '04-JAN-12' ), 'DD-MON-YY')
    if TRANS_DATE is a DATE datatype; remember to NEVER TO_DATE() any DATE datatype!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    2
    Much thanks dear. It works fine for me.

    -Shaji

  4. #4
    Join Date
    Feb 2005
    Posts
    57
    Also if TRANS_DATE contains a time element then you might need to TRUNC it depending on your requirements.

Posting Permissions

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