I'm trying to add 10 seconds to a table field using the TIMESTAMPADD function. But I'm getting all types of syntax errors. I tried all types of combinations, nothing seems to work. Oracle's web site is not too helpful.
SELECT TIMESTAMPADD(SQL_TSI_SECOND, 10, SYSDATE) FROM DUAL;
Any help would be greatly appreciated.
03-01-2001, 02:59 PM
Are you sure it's an Oracle function and not a user defined function?
We can be more helpful if you post error codes and messages.
If you have a date MY_DATE you can add 1 day with +1:
SELECT my_date+1 FROM dual;
You can add one hour with +1/24:
SELECT my_date+1/24 FROM dual;
You can add one second with +1(60*60*24):
SELECT my_date+1/(60*60*24) FROM dual;
To add 10 seconds:
SELECT my_date+10/(60*60*24) FROM dual;
03-01-2001, 03:16 PM
1 day = 86400 seconds, thus one second = 1/86400 days. Adding 1 to a date adds one day. Thus adding one second by adding 1/86400. To add 10 seconds, just add (1/86400)*10 = 1/8640.
select sysdate + 1/8640 from dual; // this returns sysdate + 10 seconds.
I found TIMESTAMPADD in a SQL reference, but I couldn't get it to work and couldn't find it in the data dictionary.
03-01-2001, 03:21 PM
Thank you very much! It seemed to have worked...
03-08-2001, 07:07 PM
Why am I having a problem adding a minute to a certain date?
I'm trying to execute the following statement:
SET my_date = my_date + 1/(60*24)
When I query the table, my_date column doesn't get updated.
Any help would greatly be appreciated.
03-08-2001, 07:30 PM
It gets updated, however your defoult date format is probably showing you just the day portion of the date, without the time part.
Select from your table like this:
SELECT TO_CHAR(my_date,'MM/DD/YYY HH24:MI:SS') FROM my_table;