function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
astroastro 

How to query for records where the LastModifiedDate is within a certain range?

I am fairly new (1 day old :D) to the Java API but have been using Apex,VF for a year now.

 

I have a java method which takes a Date as an input and then uses that date to check for records that have been updated within that day or in the future.  

 

For example if the date I recieve is such: 2009-12-10  I would like my query to function as follows:

 

String myDate = "209-12-10T00:00:00";

qr = binding.query("select ID from account where LastModifiedDate > :myDate");

 

ofcourse 

 

however, I cannot create a DateTime object in java or use a String against a DateTime field.  I have seen the Calendar object but am not sure how this would apply.

 

If anyone could help me formulate this query  that would be greatly appreciated.

 

Also, I need understand there is an issue with time's being checked in UTC?  I would need to check times in PST so how would this affect my query?

 

Thank you in advance to all that help! 

Best Answer chosen by Admin (Salesforce Developers) 
mpiercempierce

You want something like:

String queryString = (rest of your query) + " WHERE LastModifiedDate > " + theIsoDateTimeFormatter.print(yourDateTime)

All Answers

astroastro

Still cannot find a solution for this.  

 

Is calendar the way to go? 

mpiercempierce
You want to format a Date in ISO8601 format. If you're using Joda Time (which you should be), you want this formatter: ISODateTimeFormat.dateTimeNoMillis()
astroastro

Thank's for responding mpierce.  I have seen joda time mentioned by yourself on another post before. I'll check that out.

 

However, would I be able to create a jodatime instance variable of some sort and then be able to stick that variable as into the query using ':jodaVariable'?  

 

I will try playing with joda time right now and post my findings, thank you for the direction!

mpiercempierce
If you're referring to a prepared statement-like functionality with your ':' notation, Joda Time won't help with that. What it will do is format the timestamp in a way that Salesforce understands.
astroastro

hmmm so if I did need a statement like the one below, I should first create the timestamp using joda time then create a query string including the joda time stamp variable (which holds my sfdc formatted timestamp value) and then throw that query string into the binding.query call.  Does this sound right?   

 

qr = binding.query("select ID from account where LastModifiedDate > :myDate");

 

Essentially the problem is with not being able to use a variable withinin the 'LastModified > X' portion of my query.

 

Thank's again for all your help. 

 

 

 

mpiercempierce

You want something like:

String queryString = (rest of your query) + " WHERE LastModifiedDate > " + theIsoDateTimeFormatter.print(yourDateTime)

This was selected as the best answer
astroastro

ahh perfect.  don't know why I didn't see that before.  

 

Thank's mpeirce i think you hit the nail on the head with that one.  I'll try it out and see how it goes! 

astroastro

Worked perfectly thank you mpierce!

 

I used joda time to format the date and the query worked flawlessly.  Here is the code for any that are interested in the future:

 

*Note: my requirement stated that I needed to use the PST timezone

 

 

... DateTimeZone dtz = DateTimeZone.forID("America/Los_Angeles"); java.util.Calendar cal = java.util.Calendar.getInstance(); int year = cal.get(java.util.Calendar.YEAR); int day = cal.get(java.util.Calendar.DAY_OF_MONTH); int month = cal.get(java.util.Calendar.MONTH)+1; //Quick and dirty way to see if we've traversed over a month/year if(day == 1){ day = cal.get(java.util.Calendar.DAY_OF_MONTH); --day; if(month == 1){ month = 12; --year; }else --month; }else --day; DateTime dt = new DateTime(year,month,day,0,0,0,0,dtz); QueryResult qr = null; try { qr = binding.query("select ID, FirstName, Middle_vod__c, LastName, ME__c, PIMS__c, Specialty_1_vod__c, Credentials_vod__c, SID__c from Account where isPersonAccount = True AND LastModifiedDate > "+dt.toString()+" limit 5"); } catch (Exception e) {e.printStackTrace();} ...

 

 

 

Message Edited by astro on 12-03-2009 03:09 PM