Datetime datatype

[Speedy Gonzalez]

Hi people,

I want to create a trigger which automatically deletes any session records which are 1 day older then today.

So something like this will work if I used the keyword TODAY:

for each sessiontable where sessiontable.dsess < TODAY -1:
delete sessiontable.
end.

The dsess field is of the type DATE so the -1 can work for this.
However I'm using the DATETIME-TZ type. The keyword to get the time and date of this day is: NOW.

Now with this type I can't just type -1 at the end.
So my question. What is the correct syntax to get the the previous day of the set date (NOW -1 doesn't work)?

Many thanks in advance!

 

[schaapie]

I'm not sure about your case, but we use sysdate() in our Cognos IQD's and sysdate() - 1 works too.

 

[Speedy Gonzalez]

I'm not sure about your case, but we use sysdate() in our Cognos IQD's and sysdate() - 1 works too.

Hi,

Thanks for your reply. I must use the datetime-tz type but I have a way after a bit of fooling around.

This is the code:

for each sessiontable where sessiontable.dsess < NOW -TIME:
delete sessiontable.
end.

Remember that NOW is the keyword which returns the date and time of the moment it is called.

So the NOW keyword will give 10-08-2005-12:38:33-2.00GTM (sorry, don't know the exact syntax but you know what I mean)

Now I wanted to remove all session data of a user which had logged on the date which was set in the variable dsess (let's say this was 2 hours ago: 10-08-2005-10:38:33-2.00GTM.
NOW - TIME = 10-08-2005-12:38:33 - 12:38:33 = 10-08-2005-00:00:00

The check is: Is dsess < then NOW - TIME ? Well it is not. Now I can store session data for atleast a few hours. AFter that it will be deleted.

Is this OK according to you guys? Or is there something I am missing??

Many thanks!