Apex Code Development You need to sign in to do that
Don't have an account?
Tomas Mizerak Convert Date into Unix timestamp with SQOL
Hi Salesforce developers,
I am exporting standard Contact fields with Salesforce SOQL. I need to import it to other tool but I need to convert LastModifiedDate, which is ISO datetime string, into Unix timestamp seconds from 1/1/1970.
Do you have any ideas how to do that?
I am using iPaaS platform to load SOQL via API (app.tray.io, elastic.io and [Salesforce Workbench][1] for debugging issues) so I don't have access to write custom javascript or apex code.
I checked the official [Salesforce Date functions][2], but I don't see a function that I need.
**Not** working:
Thank you for your ideas!
[1]: https://workbench.developerforce.com/
[2]: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm
I am exporting standard Contact fields with Salesforce SOQL. I need to import it to other tool but I need to convert LastModifiedDate, which is ISO datetime string, into Unix timestamp seconds from 1/1/1970.
SELECT Email,LastModifiedDate FROM Contact Email LastModifiedDate 1 john.doe@trailhead.com 2020-10-27T14:06:12.000Z 2 laetitia.arevik@trailhead.com 2020-10-27T14:06:11.000Z
Do you have any ideas how to do that?
I am using iPaaS platform to load SOQL via API (app.tray.io, elastic.io and [Salesforce Workbench][1] for debugging issues) so I don't have access to write custom javascript or apex code.
I checked the official [Salesforce Date functions][2], but I don't see a function that I need.
**Not** working:
SELECT Email,FirstName,LastModifiedDate.getTime(),LastName FROM Contact SELECT Email,FirstName,DateTime(LastModifiedDate).getTime(),LastName FROM Contact
Thank you for your ideas!
[1]: https://workbench.developerforce.com/
[2]: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm
Hi,
due to my impatience to address this issue I posted the question also to stackoverflow, where I got satisfactory answer, which I am posting here as well:
source: https://stackoverflow.com/questions/64801708/is-there-soql-function-to-convert-date-to-unix-timestamp-in-salesforce-soql-or-s/
All Answers
Hi,
unfortunately salesforce soql date/datetime results cannot be formated. The functions mentioned above will work in Apex. Create a list, convert the values, export to a file, download the file, pay attention to the salesforce limits. We use for import, export functionalities an external tool (e.g. talend).
Date Formats and Literals
...
DateTime field values are stored as Coordinated Universal Time (UTC). When a dateTime value is returned in Salesforce, it’s adjusted for the time zone specified in your org preferences. SOQL queries, however, return dateTime field values as UTC values
...
If this helps, please mark as Best Answer to help others too.
Hi,
due to my impatience to address this issue I posted the question also to stackoverflow, where I got satisfactory answer, which I am posting here as well:
source: https://stackoverflow.com/questions/64801708/is-there-soql-function-to-convert-date-to-unix-timestamp-in-salesforce-soql-or-s/