PeopleSoft Wiki

Hi,

I'm assuming that the table you want to pull data from has a date/time stamp field of some sort (I'm going to use the example field LASTUPDDTTM). So you basically want to pull any rows with a last update date/time >= (system date/time the SQR runs - 24 hours)?

So for example if your process runs starts at 2:00 am on 13/02/2009, you'll want to extract any rows in the table created between 2:00 am of 12/02/2009 to 2:00 am of 13/02/2009 (the past 24 hours).

If you have a look at the dates in SQR article, you'll see a number of SQR related date functions. The one you want is dateadd. However the catch is that you want to add -24 hours (or -1 days) which in fact means subtracting 24 hours.

You'll need to store the date 24 hours ago in a variable like this:

let $dtFrom = dateadd(datenow(), 'hour', -24)

Then in your SQL you'll want to filter by rows greater than or equal to this date/time:

...
where LASTUPDDTTM >= $dtFrom
...

Of course, you can change your range from 24 hours to whatever you need.

Hope that helps.

Not sure if the date mask you have there is quite right? Should it be something like YYYY-MM-DD HH24:MI:SS? There's more information about this here.