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

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    URGENT HELP :Date Query


    Hi Guys,

    Can you people help me to write down one query:

    I have one table with a columntype "timestamp" say xyz is the name of the column.

    Suppose today is 15th Sept So I want to fetch all those records whose value is between 14th Sept 00:00:00 and 14th Sept 23:59:59.

    And date I want to keep this as a variable.

    Please help as I need it very urgently.

    Thanks.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    225
    Rep Power
    41
    This is one way to select all columns on a row for the timestamp range you specified:
    Code:
    select *
     from  schema.table
    where  timestamp between to_date('09-14-2012 00:00:00','mm-dd-yyyy HH24:mi:ss')
      and                    to_date('09-14-2012 23:59:59','mm-dd-yyyy HH24:mi:ss')
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    Urgent


    Hi,
    thanks alot for your reply.
    This is what I want to achieve,
    But how can i replace this date "09-14-2012" with SYSDATE -1.


    Originally Posted by spacebar208
    This is one way to select all columns on a row for the timestamp range you specified:
    Code:
    select *
     from  schema.table
    where  timestamp between to_date('09-14-2012 00:00:00','mm-dd-yyyy HH24:mi:ss')
      and                    to_date('09-14-2012 23:59:59','mm-dd-yyyy HH24:mi:ss')
  6. #4
  7. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    use this

    select * From table_name where xyz between (sysdate-1) and sysdate
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    Urgent


    Hi thanks for your reply.

    I want to fetch records.. whose modification date is between (SYSDATE -1 00:00:00) & (SYSDATE -1 23:59:59).

    select * From table_name where xyz between (sysdate-1) and sysdate.. this will not work as in my case xyz is of timestamp type.. and SYSDATE only returns date.
  10. #6
  11. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    then you need to concatenate only the date part with your timestamp part and use

    try like this

    select * from table_name
    where xyz between to_timestamp(to_char(trunc(sysdate-1),'mm-dd-yyyy')||' 00:00:00','mm-dd-yyyy HH24:mi:ss')
    and to_timestamp(to_char(trunc(sysdate-1),'mm-dd-yyyy')||' 23:59:59','mm-dd-yyyy HH24:mi:ss')

IMN logo majestic logo threadwatch logo seochat tools logo