1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to specify time from 12:00Am midnight to 23:59:59

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Jan 18, 2012.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    I have a query that needs "cc_messagehistory.SendTime" to be from 12:00Am midnight to 23:59:59.

    What I am trying to say is that the "cc_messagehistory.SendTime" to include Yesterday data only: Not including two-days-ago-data and not including today-data.

    The "cc_messagehistory.SendTime" is like: 17-JAN-12 10.24.17.024000000 AM

    Since the "cc_messagehistory.SendTime" field is an index, my SYSDBA says that we can not use functions with this field.

    So I need a query to show me data for only yesterday like:

    cc_messagehistory.SendTime Between Time1 and Time2

    Time1 = is yesterday 17-JAN-12 at 00:00:00:00 AM
    Time2 = is yesterday 17-JAN-12 at 23:59:59:59 PM


    Can you please show me Time1 and Time2?

    Thank you all. Today after one that I posted my question. I found the answer myself:

    And cc_messagehistory.SendTime BETWEEN Trunc(Sysdate -:sqlDayOffset) AND Trunc(Sysdate -:sqlDayOffset+1 - 1/86400) (assuming parameter sqlDayOffset==1)


    Thanks and Best Regards,
    Farshad
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This applies to Oracle and SQL in what way? Your post is confusing, to say the very least. Are you asking a question? If not, what exactly is the purpose of this offering?
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi Farshad, could you please post the query so we can understand what you are trying to do... Thanks.