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
AndreyVolAndreyVol 

SOQL Date literal TODAY is evaluated incorrectly

I am having the following problem. When I use TODAY literal in my SOQL statements comparing with ActivityDate for some reason the records where date = tomorrow are returned. Here are the details:

Code:
Select e.ActivityDate, e.ActivityDateTime, e.Id, e.Subject from Event e  
Where e.OwnerId = '<myId>'  AND e.ActivityDate = TODAY

Today is 05/18/2007


The statement does not return events where ActivityDate is 05/18/2007. Instead in returns events where ActivityDate is 05/19/2007.

When I filter by a Datetime field (Event.ActivityDatetime) this does not occur and correct records are returned.


For now, I just simply modified my SOQL to use YESTERDAY instead of TODAY. But I would like to know the reason for this behavior. Am I missing something here or this could be an issue?


Thank you.
sfdcfoxsfdcfox
All of the date literals are offset by the user's time zone settings. If you don't live in London, the API will return dates that appear to be outside of the date literal (eg. TODAY returning items for tomorrow), but they're really not (because the times are returned in GMT). For example, if you live in California, and you choose TODAY, and you have an activity for 5/23/2007 5pm, the date/time returned will be 5/24/2007 1am. Which is tomorrow in London (GMT), but it's today for you, since you're in California. This also applies to LAST_N_DAYS, THIS_MONTH, etc. The results will always be correct for the user running the query, which will be different for each user. Use GMT values if you would prefer to get consistent results across all users.

~ sfdcfox ~
AndreyVolAndreyVol
When I run queries against datetime fields, the TODAY literal works just as expected. My API user time offset is configured correctly for my timezone and all the dates are returned as expected.

The problem occurs when I query Date fields (not Datetime). So, it appears that Date fields are not offset by the user settings as Datetime fields are... Instead they are returned always in GMT. To me this seems inconsistent...

Message Edited by AndreyVol on 05-24-2007 06:35 PM
SFDC Dev-adminSFDC Dev-admin
I have the same problem ... we are trying to query leads based  on a custom date field. Lead_expiration_date__c ( custom field) > TODAY.  but when this query is run in CST it returns lead with the current date as well . API user used to runthis query is set up on MST time zone.

Select id from lead where Lead_expiration_date__c > TODAY
AK2017AK2017
It should be System.Today()
  
Select e.ActivityDate, e.ActivityDateTime, e.Id, e.Subject from Event e  <br>Where e.OwnerId = '<myId>'  AND e.ActivityDate = System.TODAY()

 
Suraj Tripathi 47Suraj Tripathi 47
Hi AndreyVol,

In place of Today, you should use Date.Today().
So you have to filter using the below one:
ActivityDate = Date.Today()

If you find your Solution then mark this as the best answer. 

Thank you!

Regards 
Suraj Tripathi