+ Start a Discussion
BrannonkBrannonk 

How to Format Date or Date time in Visualforce Object query?

I have been trying to use the new Visualforce Object for some basic javascript functions, but have a tough time using a retrieve with a date clause.

example sniplet :
var c = new SObjectModel.Campaign(); c.retrieve(function(){return({ limit : 100, where: {StartDate: { gt: '2014-12-31' }}});}, function (err, records) { if (err) { console.log(err); } else {


I have formatted the date in miliseconds, utc string (2014-12-31T23:59:59Z), and a few others. So far always get ther error:
"Error: Error occurred while performing RETRIEVE operation on sobject: Campaign with data: {limit=100, where={StartDate={gt=2014-12-31}}} (INVALID_FIELD: type, id FROM Campaign WHERE StartDate > '2014-12-31' LIMIT 100 ^ ERROR at Row:1:Column:81 value of filter criterion for field 'StartDate' must be of type date and should not be enclosed in quotes)"

I have tried it for other objects that have DateTime formats ( ie CreatedDate ) wit the same problems.
Has anyone solved this? Maybe us moment.js to set a specific format?

Thanks for your help!
 
Best Answer chosen by Brannonk
Philip NelsonPhilip Nelson
I can't confirm whether this works, but try creating a date object:

var day = new Date(2014, 12, 31, 23, 59, 59, 59);

Then include that in your JSON retrieve request.

If that doesn't work, try passing in day.getTime().
 

All Answers

Philip NelsonPhilip Nelson
Brannonk, it looks like you end up having quotes around the dateTime literal in the submitted query string, which isn't permitted. Otherwise, your dateTime formatted as 2014-12-31T23:59:59Z should work fine.
BrannonkBrannonk
Thanks Philip!
Do you know to pass the javascript string so that there are no quotes? I tried this and quotes are still added automatically:
var day = "2014-12-31T23:59:59Z";
var c = new SObjectModel.Campaign(); c.retrieve(function(){return({ limit : 100, where: {StartDate: { gt: day }}});}, function (err, records) { if (err) { console.log(err); } else {
 
Philip NelsonPhilip Nelson
I can't confirm whether this works, but try creating a date object:

var day = new Date(2014, 12, 31, 23, 59, 59, 59);

Then include that in your JSON retrieve request.

If that doesn't work, try passing in day.getTime().
 
This was selected as the best answer
BrannonkBrannonk
thank you!
Here is what I got working (with some date math help from moment.js)

var tdy = moment();
var day = tdy.subtract(3, 'months');
                     var c = new SObjectModel.Campaign();
                        c.retrieve(function(){return({ limit : 100, where: {StartDate: { gte: day.format("YYYY[-]MM[-]DD[T]HH[:]mm[:]ss") }}});},
                         function (err, records) {