Post reply

Date Selection Nightmare

  • November 12, 2004 at 8:28 am

    #59799

    Hi Folks,

    I connect to a SQL box in a different timezone. It stores dates in pacific time, but I need to do date queries in EST (3 hour differnce).

    Thus I do this:

    SELECT CONVERT(smalldatetime, og.order_create_date, 20),  og.ordergroup_id,  oli.product_id,   og.subtotal,   oli.quantity,          oli.unit_price

    FROM ordergroup og   INNER JOIN orderformlineitems oli ON oli.ordergroup_id = og.ordergroup_id 

    WHERE og.order_create_date > '2004-10-28 21:00:00'  AND og.order_create_date <  '2004-10-29 20:59:59'   AND order_number NOT LIKE 'OP-%'  ORDER BY og.order_create_date

    The trouble is that the last record returned from this query has a date of '2004-10-29 21:00:00'. Why is it returning this when I want og.order_create_date <  '2004-10-29 20:59:59'???!!!!

    Thanks,

    Disco

  • November 12, 2004 at 8:53 am

    #529948

    Some one just pointed out to me that smalldatetime has an accuracy of one minute.

    Stupid stupid stupid!

  • November 12, 2004 at 1:39 pm

    #530010

    Laughing. No sense getting frustrated about it. It's how we learn. You don't know how many times I've been quizzed by developers when I tell them a SQL integer is 2GB. They are determined to make it the same size as their languages.

    Quand on parle du loup, on en voit la queue

  • November 13, 2004 at 6:14 pm

    #530035

    Rather than messing around with dates and times, why not just add the three hours to the Pacific Time date (see DATEADD in code below)?

    Also, you may have missed some of the entries because you only used ">" in your first criteria and you used "< 20:59:59" which means you exclude the final moments of the translated day.

     
    SELECT CONVERT(datetime, og.order_create_date, 20),
           og.ordergroup_id,
           oli.product_id,
           og.subtotal,
           oli.quantity,
           oli.unit_price
      FROM ordergroup og   
     INNER JOIN orderformlineitems oli
             ON oli.ordergroup_id = og.ordergroup_id 
    WHERE DATEADD(hh,3),og.order_create_date) >= '2004-10-28 00:00:00' 
      AND og.order_create_date <  '2004-10-29 00:00:00'
      AND order_number NOT LIKE 'OP-%' 
    ORDER BY og.order_create_date

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply