Results 1 to 8 of 8

Thread: TRUNC Date

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: TRUNC Date

    Hey Guys,

    I am trying to run a report that looks at all entries from the previous day.
    So far I have tried to use:

    field_name <= sysdate -1

    Which works fine but obviously depending on what time of the day I run the report will only find data 24hrs to the time the report was run. I have tried using:

    field_name = TRUNC(sysdate -1)

    but it doesn't bring me back anything!

    Any ideas why?

    Lucy

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try this in sqlplus ...

    select to_char(trunc(sysdate)-1, 'dd-mon-yy hh24:mi:ss') from dual

    trunc(sysdate)-1

    HTH
    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Talking TRUNC Date

    Thanks Gregg, ill give this a go in the morning at work ATM I have the trunc(sysdate)-1 in my where clause. Will the below still work seeing its another select statement?



    Quote Originally Posted by gbrabham
    Try this in sqlplus ...

    select to_char(trunc(sysdate)-1, 'dd-mon-yy hh24:mi:ss') from dual

    trunc(sysdate)-1

    HTH
    Gregg

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by lucyg_2000

    field_name = TRUNC(sysdate -1)

    but it doesn't bring me back anything!

    Any ideas why?
    Because TRUNC(sysdate -1) contains only the date part, while field_name still has the time part in it too...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Yes, it will work as part of the where clause ... you are comparing a date to a function that returns a date.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you have an index on field_name then do the following

    Code:
    field_name between  TRUNC(sysdate -1) and trunc(sysdate) - (1/86400)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by beilstwh
    Code:
    field_name between  TRUNC(sysdate -1) and trunc(sysdate) - (1/86400)
    Or equivalently (for those of us who always forget 86,400 and have to work it out :
    Code:
        field_name >= TRUNC(sysdate -1) 
    and field_name <  TRUNC(sysdate)

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Tony,
    I like your way better.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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