function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
bill hayhowbill hayhow 

Office Toolkit v4 error

This SQL works fine with Office Toolkit v3, but not v4:

 

select * from Billing_CCPayment__c
WHERE Status__c = 'Unsuccessful'
AND Payment_Process_Date__c >= 2009-04-03
AND Payment_Process_Date__c <= 2009-04-03T23:59:59-05:00

 

In v4, I get this error:

 

SForceOfficeToolkit4.SForceSession4.1 (0x80004005)
INVALID_FIELD: Status__c = 'Unsuccessful' AND Payment_Process_Date__c >= 2009-04-03 ^ ERROR at Row:1:Column:373 value of filter criterion for field 'Payment_Process_Date__c' must be of type dateTime and should not be enclosed in quotes

 

It appears that my dates/times are formatted as per the v4 documentation.  Payment_Process_Date__c is specified as a date/time field in Salesforce. 

 

Has something changed with the 'where clause' syntax - or with date formatting syntax?

 

Thanks for helping.

foghornfoghorn

2009-04-03T23:59:59-05:00

 

is - 05:00 (timezone offset?) legal in a salesforce query?

bill hayhowbill hayhow

Here are examples from the documentation:

• YYYY-MM-DDThh:mm:ss+hh:mm • 1999-01-01T23:01:01+01:00

• YYYY-MM-DDThh:mm:ss-hh:mm • 1999-01-01T23:01:01-08:00

• YYYY-MM-DDThh:mm:ssZ • 1999-01-01T23:01:01Z

 

This technique worked in Toolkit v3, but not in v4.
foghornfoghorn

ah, how about the date only filter?

2009-04-03 

bill hayhowbill hayhow
I tried 'date only', and it failed too.  Only when I removed the date fields entirely did the query succeed.
foghornfoghorn

I mean, for the constant value 2009-04-03, does it work if you add a time portion?

 

2009-04-03T00:00:00

bill hayhowbill hayhow

It doesn't appear to be related to time.

 

I just tried the query like this:

 

select * from Billing_CCPayment__c
WHERE Status__c = 'Unsuccessful'
AND Payment_Process_Date__c >= 2009-03-01

and this query failed with the same error message.

 

SForceOfficeToolkit4.SForceSession4.1 (0x80004005)
INVALID_FIELD: Status__c = 'Unsuccessful' AND Payment_Process_Date__c >= 2009-03-01 ^ ERROR at Row:1:Column:373 value of filter criterion for field 'Payment_Process_Date__c' must be of type dateTime and should not be enclosed in quotes

 

When I tried it with a time as per your example:

 

select * from Billing_CCPayment__c
WHERE Status__c = 'Unsuccessful'
AND Payment_Process_Date__c >= 2009-03-01T00:00:00

 

I got a different error:

 

SForceOfficeToolkit4.SForceSession4.1 (0x80004005)
MALFORMED_QUERY: Payment_Process_Date__c >= 2009-03-01T00:00:00 ^ ERROR at Row:1:Column:419 unexpected char: 0x?F
foghornfoghorn

So,

2009-04-03T00:00:00-05:00

 

for midnight EST, or

 

2009-04-03T05:00:00Z

 

**The error is a normal salesforce query response, the officetoolkit does not do any processing on the where clause (though it does process the select list for the * in the select list)

bill hayhowbill hayhow

I tried with this:

 


select * from Billing_CCPayment__c
WHERE Status__c = 'Unsuccessful'
AND Payment_Process_Date__c >= 2009-04-03T00:00:00-05:00

 

and it worked.

So then time is now a required part of a date when included in a query?  Is that right?  Rather odd.
SuperfellSuperfell
Yes, for dateTime fields you must specify a full dateTime value, this changed a number of releases ago, and was done so that there was no ambiguity / confusion as for what the missing time part would be treated as.