Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Thumbs up Unanswered: Oracle: date calculation

    Folks, I'm simply trying to ask Oracle what is the date 7 days before a named date. I know I can use a SELECT statement to obtain this value somehow (not on any particular table, if possible). This is within a perl script where I need use other database calls to Oracle and I'd like to avoid having to install the Date::Calc CPAN module.

    Can anyone help me to have Oracle perform this date manipulation for me?

    thanks,
    - Mark

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oracle is actually very good for date manipulation. A date is basically held as a fixed point number where 1 represents 1 day.

    So for the date 7 days before a given date:

    select to_date('01-jan-2008','dd-mm-yyyy')-7 from dual

    So to look for records in the last 7 days (from the current time i.e. current time-(7*24 hours)) you can do:

    select * from table where date_column>=sysdate-7

    for the last 7 days from midnight

    select * from table where date_column>=trunc(sysdate)-7

    Using the same technique you can select data from the last n hours or minutes i.e. for data in last 10 minutes

    select * from table where date_column>=sysdate-10/1440

    (1440 is the number of minutes in a day)

    Lots of other handy date time stuff here :
    date time types

    date time functions

    Alan

  3. #3
    Join Date
    Sep 2008
    Posts
    2

    Talking

    Yes... that's it!! Thanks!!

Posting Permissions

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