#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    2
    Rep Power
    0

    How to subtract one minuite from current hour


    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,
    Vissu
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    calculating a value "slightly less" than a given value -- for example in your case subtracting 1 minute -- is a symptom of using BETWEEN

    solution: don't use BETWEEN, use an upper open end range test
    Code:
    WHERE value  >= {?From Time} 
      AND value   < {?To Time}

    Comments on this post

    • ishnid agrees
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo