Per the docs on the FORMAT() function, I can localize numeric, datetime, and currency fields in an SOQL query by wrapping the field name in FORMAT(). This does not work for date fields; I either get an empty result or a JSON parsing error. Specifically,

SELECT Id, Format(CloseDate) FROM Opportunity


You're trying to decode an invalid JSON String {"totalSize":35,"done":true,"records":[{"attributes":{"type":"Opportunity","url":"/services/data/v40.0/sobjects/Opportunity/0064100000ACH41AAH"},"Id":"0064100000ACH41AAH","CloseDate"}]}[{"message":"java.lang.String cannot be cast to java.util.Date","errorCode":"JSON_PARSER_ERROR"}]: {"totalSize":35,"done":true,"records":[{"attributes":{"type":"Opportunity","url":"/services/data/v40.0/sobjects/Opportunity/0064100000ACH41AAH"},"Id":"0064100000ACH41AAH","CloseDate"}]}[{"message":"java.lang.String cannot be cast to java.util.Date","errorCode":"JSON_PARSER_ERROR"}]

I do not run into this with numeric, currency, or datetime fields. Is this a known issue and is there a good workaround?

  • 1
    Please add your snippet which you are using for deserializing, you might get better support with that. – Raul Jun 14 '17 at 20:21
  • This is in the Developer Console; I'm not doing the deserializing. When I make the query using the REST query API, I get: {"totalSize":1,"done":true,"records":[{"attributes":{"type":"Opportunity","url":"/services/data/v37.0/sobjects/Opportunity/0064100000ACH41AAH"},"Id":"0064100000ACH41AAH","editDate"}]}[{"message":"java.lang.String cannot be cast to java.util.Date","errorCode":"JSON_PARSER_ERROR"}] which is indeed not valid JSON. – Neal Groothuis Jun 16 '17 at 3:04
  • @NealGroothuis I am facing the same issue with the Date field. How did you resolve this? Did you find any workaround for this issue? – Sagar Thoriya Apr 13 at 10:18
  • I didn't. We're localizing it ourselves, which means we don't have the benefit of knowing what SF believes the locale of the user is. – ngroot Apr 14 at 19:05

I guess you used the Query Editor in the developer console and got that error. However, if you use the Format() in code, I think it would work.

Opportunity opp = [SELECT Id, format(CloseDate) editDate FROM Opportunity limit 1];
  • I did indeed use the Query Editor in the developer console. However, I get the same error when I submit the query using the REST query API. – Neal Groothuis Jun 16 '17 at 2:51
  • Yes, it is. -.- – daniel wu Jun 16 '17 at 3:13

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.