Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2013
    Posts
    25

    Unanswered: How to subtract 1 minute from time

    Hi experts,

    I have a query contains from time and to time parameters.
    I need to subtract 1 minute from totime parameter.
    I used totime-1.
    It is working in database level and it is not working in my crystal reports.I mean it is subtracting 1 hour in report level.
    Can any one please tell me other formula for subtracting a minute.
    Please find the where condition in my query

    where
    ccsr.store_date = {?Transaction Date}
    and ccsr.value_type='B'
    and (cast(time_format(ccsr.transaction_time,'%H') as unsigned) between
    {?From Time} and ({?To Time}-1))
    group by
    ccsr.brand_code
    ) cons




    Thanks in advance,
    Divya

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Obviously your code was snipped from a larger piece, but I'd try to modify it like:
    Code:
       WHERE  ccsr.store_date = {?Transaction Date}
          and ccsr.value_type='B'
          and (cast(time_format(ccsr.transaction_time,'%H') as unsigned)
             between {?From Time} and ({?To Time}- INTERVAL 1 Minute))
       GROUP BY ccsr.brand_code
    
    ) cons
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2013
    Posts
    25
    Hi PatP first of all thank you,

    i tried with your suggestion but i am not getting any data with that code.
    Please find my query and suggest,

    SELECT
    cons.*,
    IFNULL(ROUND((cons.actSale-cons.tarSale)/cons.tarSale*100,1),'-') varSale,
    IFNULL(ROUND((cons.actFootfall-cons.tarFootfall)/cons.tarFootfall*100,1),'-') varFootfall,
    IFNULL(ROUND((cons.actCon-cons.tarCon)/cons.tarCon*100,1),'-') varCon,
    IFNULL(ROUND((cons.actAtv-cons.tarAtv)/cons.tarAtv*100,1),'-') varAtv,
    IFNULL(ROUND((cons.actIpc-cons.tarIpc)/cons.tarIpc*100,1),'-') varIpc,
    IFNULL(ROUND((cons.actSpmh-cons.tarSpmh)/cons.tarSpmh*100,1),'-') varSpmh
    FROM
    (SELECT
    ccsr.brand_code,
    ccsr.brand_name,
    -- ccsr.category,
    IFNULL(ROUND(SUM(ccsr.target_sale),1),0) tarSale,
    IFNULL(ROUND((SUM(ccsr.target_transaction_count)/SUM(ccsr.target_footfalls))*100,1),0) tarCon,
    IFNULL(ROUND((SUM(ccsr.target_sale)/SUM(ccsr.target_transaction_count)),1),0) tarAtv,
    IFNULL(ROUND((SUM(ccsr.target_quantity)/SUM(ccsr.target_transaction_count)),1),0) tarIpc,
    IFNULL(ROUND((SUM(ccsr.target_sale)/SUM(ccsr.target_man_hours)),1),0) tarSpmh,
    SUM(ccsr.target_transaction_count) tarTransCount,
    SUM(ccsr.target_quantity) tarQuantity,
    SUM(ccsr.target_man_hours) tarManHours,
    SUM(ccsr.target_footfalls) tarFootfall,


    IFNULL(ROUND(SUM(ccsr.actual_sale),1),0) actSale,
    IFNULL(ROUND((SUM(ccsr.actual_transaction_count)/SUM(ccsr.actual_footfalls))*100,1),0) actCon,
    IFNULL(ROUND((SUM(ccsr.actual_sale)/SUM(ccsr.actual_transaction_count)),1),0) actAtv,
    IFNULL(ROUND((SUM(ccsr.actual_quantity)/SUM(ccsr.actual_transaction_count)),1),0) actIpc,
    IFNULL(ROUND((SUM(ccsr.actual_sale)/SUM(ccsr.actual_man_hours)),1),0) actSpmh,
    SUM(ccsr.actual_transaction_count) actTransCount,
    SUM(ccsr.actual_quantity) actQuantity,
    SUM(ccsr.actual_man_hours) actManHours,
    SUM(ccsr.actual_footfalls) actFootfall

    FROM
    cust_conv_store_report ccsr
    WHERE
    ccsr.store_date = DATE_FORMAT(STR_TO_DATE('18/05/2012','%d/%m/%Y'),'%Y-%m-%d')
    AND ccsr.value_type='B'
    AND (CAST(TIME_FORMAT(ccsr.transaction_time,'%H') AS UNSIGNED) BETWEEN 10 AND (16- INTERVAL 1 MINUTE))
    GROUP BY
    ccsr.brand_code
    ) cons


  4. #4
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    You are aware that the result from STR_TO_DATE is already a valid date format?
    By the way, the following expressions will have the same result:
    Code:
    WHERE HOUR(transaction_time) BETWEEN 10 AND (16-1)
    --
    WHERE transaction_time BETWEEN '10:00' AND ('16:00' - INTERVAL 1 MINUTE)

  5. #5
    Join Date
    Mar 2013
    Posts
    25
    Hi Gervs.
    I tried with this code but i am not getting any data.Please check

    WHERE transaction_time BETWEEN '10:00' AND ('16:00' - INTERVAL 1 MINUTE)

  6. #6
    Join Date
    Mar 2013
    Location
    Breda, Netherlands
    Posts
    15
    I see that you're using Crystal Reports. In combination with Visual Studio?

  7. #7
    Join Date
    Mar 2013
    Posts
    25
    Hi Gervs,

    Yes i am using Crystal reports 2011.My problem is in query level it is subtracting 1 minute but in report level it is subtracting 1 hour.Please suggest

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use one of the intrisic MySQL date/time functions such as datesub
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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