I have to run a load every 6 AM in the morning, so just wanted those records that have been updated or created since this time i.e yesterday 6 AM and today 6 AM. I was thinking to use a logic to add 6 hours in YESTERDAY function to make it 6 am 7 subtract 18 hours from TODAY function to make it 6am, but evrytime i do this i receive an error sometime mall function query or sometime bind functions not allowed. Can anyone provide me the exact query for the same ? This is on high priority, i am using jitterbit for this.

  • Why don't you just do all records updated yesterday? It's a lot simpler and does 6 hours really make a difference? – Nick Cook Sep 4 '15 at 4:11
  • 1
    If you're using some job control system to do you batch loads, I would recommend using an additional field in salesforce to hold the job number that most recently touched that record. That way, immediately after your batch load you can just select all records with the job number your job control system gives you! – Christian Carter Feb 1 '16 at 16:58

You can use Date Formats and Date Literals together with Date Functions directly in the SOQL query:

Select Id 
From Account
Where CreatedDate = TODAY
And HOUR_IN_DAY(CreatedDate) = 6

You could do a SOQL query with System.Now() for current time and System.Now()-1 for yesterday's current time like below.

List<Case> c = [select id from case where createddate <: system.now() and createddate >: system.now()-1];

If the batch starts execution by 6.03 AM today, it will fetch all the records created between 6.03 AM today and 6.03 AM yesterday.


If the minute level accuracy is required we could do the following in apex batch class but not sure how to do that in informatica.

DateTime dt1 = Datetime.newInstanceGMT(System.now().year(),System.now().month() ,System.now().day(),System.now().hour(),0,0);
DateTime dt2 = Datetime.newInstanceGMT(System.now().year(),System.now().month() ,System.now().day()-1,System.now().hour(),0,0);
List<Case> c = [select id from case where createdDate <: dt1 and createdDate >: dt2];

Hope it helps.

  • The trouble with this is a batch process starts when resources become available, meaning each day it might run at a different time. i.e. It might run one day at 6:03 but the next day at 6:11. In this case, any record edited between 6:03 and 6:11 on the previous day won't be updated. – Nick Cook Sep 4 '15 at 5:29
  • Updated my answer to include minute level accuracy. But this i checked in Execute Anonymous block but not sure whether its possible in Informatica or not. – Vigneshwaran G Sep 4 '15 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.