2
{!REQUIRESCRIPT("/soap/ajax/29.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/29.0/apex.js")} 


var accountid= "{!Account.Id}"; 

var nowDate = new Date(); 
var d= (nowDate.getFullYear()-1)+'-'+(nowDate.getMonth()+1)+'-'+nowDate.getDate(); 
alert(d);

var RelatedOpps= sforce.connection.query("SELECT Id,Cohort_Date_Value__c,StageName from Opportunity WHERE Accountid ='{!Account.Id}' AND Cohort_Date_Value__c<"+d);

if(RelatedOpps==null){  
alert("No Opps "+RelatedOpps);
} 
else { 
alert("List of Opps "+RelatedOpps);

}

What I am doing: I am trying to query the Opportunity records based on Cohort_Date_Value__c value. Trying to query records where Cohort_Date_Value__c<(Today()-1 year)

Cohort_Date_Value__c: Datatype: Formula(Date): Ex:2017-02-04 d: Ex:2017-2-4

Error: enter image description here

Any idea how to compare these dates?

UPDATE: (Working)

{!REQUIRESCRIPT("/soap/ajax/29.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/29.0/apex.js")} 


var accountid= "{!Account.Id}"; 

var todaysDate = new Date();

function convertDate(date) {
  var yyyy = (date.getFullYear()-1).toString();
  var mm = (date.getMonth()+1).toString();
  var dd  = date.getDate().toString();

  var mmChars = mm.split('');
  var ddChars = dd.split('');

  return yyyy + '-' + (mmChars[1]?mm:"0"+mmChars[0]) + '-' + (ddChars[1]?dd:"0"+ddChars[0]);
}
var d = convertDate(todaysDate);
alert(d);

var RelatedOpps= sforce.connection.query("SELECT Id,Cohort_Date_Value__c,StageName from Opportunity WHERE Accountid ='{!Account.Id}' AND Cohort_Date_Value__c < " + d);

if(RelatedOpps==null){ 

alert("No Opps "+RelatedOpps);

} 
else { 

alert("List of Opps "+RelatedOpps);
records = RelatedOpps.getArray("records");

for (var i=0; i< records.length; i++) {
    var record = records[i];
    alert("Opportunity name"+ record.Name + "Contact Id -- " + record.Cohort_Date_Value__c);
  }

}
  • check my answer of simplified approach – Santanu Boral Feb 4 '17 at 20:07
0

If you are trying to compare with the formula Date field then, convert the today's date with proper format.

var d = (new Date().toISOString()).substring(0,10); 

which will always give you YYYY-MM-DD format.

To retrieve last year on same date:

var currentDate = (new Date().toISOString()).substring(0,10);   
var yearPart = (new Date().toISOString()).substring(0,4) -1;
d = yearPart + (new Date().toISOString()).substring(4,10);

And then in the query, use like this:

var RelatedOpps= sforce.connection.query("SELECT Id,CreatedDate ,StageName from Opportunity WHERE Accountid ='{!Account.Id}' AND Cohort_Date_Value__c <" + d);

Your entire code will be:

{!REQUIRESCRIPT("/soap/ajax/29.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/29.0/apex.js")} 

var accountid= "{!Account.Id}"; 
var currentDate = (new Date().toISOString()).substring(0,10);   
var yearPart = (new Date().toISOString()).substring(0,4) -1;
d = yearPart + (new Date().toISOString()).substring(4,10); 

var RelatedOpps= sforce.connection.query("SELECT Id,Cohort_Date_Value__c,StageName from Opportunity WHERE Accountid ='{!Account.Id}' AND Cohort_Date_Value__c < " + d);
if(RelatedOpps==null){ 
    alert("No Opps "+RelatedOpps);
} 
else {     
alert("List of Opps "+RelatedOpps);
records = RelatedOpps.getArray("records");

for (var i=0; i< records.length; i++) {
    var record = records[i];
    alert("Opportunity name"+ record.Name + "Contact Id -- " + record.Cohort_Date_Value__c);
  }
}
  • Thanks Santanu. However I need to minus one year from current date. Because I have to query the records last 365 days from today. I am trying this one " d.setYear(d.getYear() – 1);", but it is not working – Ajay Feb 4 '17 at 20:30
  • Sorry, I misunderstood, but I have updated any answer properly. – Santanu Boral Feb 4 '17 at 21:02
0

There is another way to accomplish what you're trying to do.

You can use SOQL Date Literals to do this without additional javascript.

From the sound of it, you'd want to use LAST_N_DAYS:n with 'n' replaced with 365. From the page linked above:

For the number n provided, starts 00:00:00 of the current day and continues for the past n days.

So n = 365 gives you a date range from today, and stretches back 365 days. Finding records strictly less than this range should give you what you're looking for.

Your query would become

var RelatedOpps= sforce.connection.query("SELECT Id,Cohort_Date_Value__c,StageName from Opportunity WHERE Accountid ='{!Account.Id}' AND Cohort_Date_Value__c < LAST_N_DAYS:365");

The upside to this method is that you don't need to worry about doing date arithmetic yourself.

The downside is that leap years are probably not handled correctly.

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.