Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Question Unanswered: Execute Immediate and Interval

    I am trying to write a function in Oracle that works similar to the DATEADD feature in SQL Server. Since we are using 9i I am able to do this by simply using the "interval" function in Oracle. I am trying to have a datepart, number and date passed in as variables. A timestamp will be returned after the calculations are made. I am trying dynamically create a statement that will do something like this:

    select sysdate + interval '1' hour into variable from dual;

    where sysdate would be the date passed in, the '1' would be the number passed in and and hour represents the datepart passed in. I know this has to be simple but because the number passed in must be in quotes I am having a heck of a time creating the dynamic sql statement. Any ideas on how to do this would be greatly appreciated!

    Thanks,
    George

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    if you are adding hours to the sysdate then:

    select sysdate+(1/24)
    into v_variable
    from dual;

    if you want the hour dynamically you could maybe do:
    select sysdate+(v_num/24)
    into v_variable
    from dual;


    would that work?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Posts
    4

    Interval

    Thanks for the incredibly quick reply! And yes, that would work. I was just trying to avoid all of the convolutions for doing that because I will need to work with days, hours, minutes, seconds, etc. Using the interval option just made that so darn easy...well, until I tried to do it this way <grin>.

    Thanks,
    George

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you could convert everything to seconds in order to avoid anything weird ...

    so sysdate plus 1 minute would be:

    select sysdate+(60/86400)


    the conversion would still get tricky for months since you would need to convert that to days then seconds.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2003
    Posts
    4
    This is true. Thanks for the input. And yes, months would get pretty ugly! <grin>


    Originally posted by The_Duck
    you could convert everything to seconds in order to avoid anything weird ...

    so sysdate plus 1 minute would be:

    select sysdate+(60/86400)


    the conversion would still get tricky for months since you would need to convert that to days then seconds.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well if your writing a function to add the interval then if it is months just use add_months().

    Alan

Posting Permissions

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