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.
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;
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.
Thank you very much! It seemed to have worked...
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.
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;
Thanks, it worked.