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
Dwayne TaylorDwayne Taylor 

SOQL Date Locale - Can filter by Locale date range with LAST_WEEK but the SELECT DATE comes out as UTC

Hi Team,

I have been working with Analytics Edge for large data dumps into excel which has worked well up until now (great tool fyi), I have realised that I have an issue with dates. I can filter the data "WHERE CreatedDate = LAST_WEEK" and it respects our Locale settings in Salesforce but the SELECT CreatedDate is coming out as UTC, so being in Melbourne +11 GMT everything before 11am is coming through as yesterday.

Example the filter is Monday to Sunday last week (7th being the beginning of the week)
Salesforce Data as presented 07/03/2016 06:00
SOQL Data 2016-03-06T19:00:07.000+0000

Ideally I would rather the output be in the same format DD/MM/YYYY but handling this in excel, but really need the date being correct with +11 timezone.

How is this generally handled on a SQL level?

Dwayne
Daniel BallingerDaniel Ballinger
In terms of API transfers, dateTime fields are always sent in UTC. See https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/primitive_data_types.htm#i1435039
 > They are always transferred in the Coordinated Universal Time (UTC) time zone.
 
You will need to handle the timezone in the external tool that is processing the data.
Dwayne TaylorDwayne Taylor
Thanks Daniel,

Bit of a bugger, built the formula for those who maybe chasing it in Excel
Where AC19 = 2016-03-06T19:00:07.000+0000 and 13 being 24 - 11 GMT

=IFERROR(IF(MID(LEFT(AC19,FIND(":",AC19)-1),FIND("T",AC19)+1,LEN(AC19))>13,DATEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000","")&" ")-1))+1,DATEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000","")&" ")-1))),"")

Dwayne
Dwayne TaylorDwayne Taylor
Updated formula, logic was reading text for a numeric test on the hour

=IFERROR(IF(VALUE(MID(LEFT(AC19,FIND(":",AC19)-1),FIND("T",AC19)+1,LEN(AC19)))>13,DATEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000","")&" ")-1))+1,DATEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(AC19,"T"," "),".000+0000","")&" ")-1))),"")