Custom button Javascript Date compare

Question

{!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:

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);
  }

}

Answer 1

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);
  }
}

Answer 2

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.