Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime

SOQL Date functions are pretty useful for grouping or filtering data by date fields. With a proper Date function used in the SOQL, the code can potentially limit the query result records a lot. e.g. Query all Tasks that are created today:

List<Task> = [
        select Id, WhatId, Subject, CreatedDate
        from Task
        where DAY_ONLY(CreatedDate) =];
The above code looks neat enough although the function DAY_ONLY is not that obviously named. The documentation states: "Returns a date representing the day portion of a dateTime field." so it should be safe enough. I used it in a few places and it worked very well. However recently I got a failing unit test while I was creating a managed package. The unit test was testing the logic that uses the above code. The test only fails when it runs after 5pm GMT-7 and before 12am GMT-7. The local time zone of the org is GMT-7.

I started debugging the issue in that precious 7-hour window period. I created a sample Task after 5pm GMT-7 and ran the above code immediately and it returned no records! I changed “” to “ + 1” in the where clause, re-ran the code and it returned the Task I just created. What? Is this saying “Get me all Tasks created tomorrow”? Obviously not, the only explanation on this is that the DAY_ONLY() function takes Datetime parameter as GMT time. Any time between 5pm GMT-7 and 12am GMT-7 is already tomorrow’s time GMT.

All of a sudden I realized that all those Date functions like CALENDAR_YEAR, DAY_IN_MONTH etc. could be useless as they are all having the same timezone issue. That cannot be right. I got back to the documentation and found it has this statement:

“SOQL queries in a client application return dateTime field values as Coordinated Universal Time (UTC) values. To convert dateTime field values to your default time zone, see Converting Time Zones in Date Functions.”

On the “Converting Time Zones in Date Functions” web page, it says “You can use convertTimezone() in a date function to convert dateTime fields to the user’s time zone.”. What do you mean by “You CAN…”? In an org with a different timezone other than GMT, you HAVE to use this method to make it working! This is obviously a compromised solution of fixing the original timezone issue in those Date functions.

Generally speaking, the timezone issue is introduced because in Apex, Datetime and Date are not clearly differentiated and Salesforce badly handles these two types. See some other Datetime issues in this blog post: Danger: Date value can be assigned to a Datetime variable.

Anyway, the fix of the above code is:

List<Task> = [
        select Id, WhatId, Subject, CreatedDate
        from Task
        where DAY_ONLY(convertTimezone(CreatedDate)) =];


Apex controller class “without sharing” in practice

A general practice of defining a controller is to prepend “with sharing” key word to “class”. This is to enforce the sharing rules that apply to the current user as otherwise sharing rules aren’t taken into account during code execution. Recently in a project I came across a requirement in which “without sharing” plays a role.

We have a custom object called “Payment” and a Approval Process is defined on this object. The requirement is that system should allow any users other than the user who submitted Payments for approval to bulk approve or reject Payment approval requests. Originally we focused on implementation of bulk approve/reject logic. We created a custom controller and a Visualfore page to did all it needs. It felt so good that we could programmatically approve/reject approval requests in a bulk in Apex code. But we got a bug from the customer saying “It only allows the manager of the users who submitted Payments for approval to approve/reject the Payments”. When other users are approving/rejecting Payments, they get the following error:

Process failed. First exception on row 0; first error: INSUFFICIENT_ACCESS_ON_CROSS_REFERENCE_ENTITY,  insufficient access rights on cross-reference id: []

The debug log shows that this error is thrown when Approval.process() method is invoked. This actually makes sense as the controller class has “with sharing” declared. Normally if a user submitted an object for approval, only the user that is assigned to this approval request (usually the manger of the submitting user) has the access rights to corresponding Approval Process work items. Other users won’t even be able to see the actions links on the form. Since the approval requests are programmatically processed via a page and a “with sharing” controller, a random person will be able to run the code but does surely not have the access rights to Approval Process work items. This is proved by the Salesforce documentation on Using the “with sharing” or “without sharing” Keywords. Related statement:

Enforcing the current user’s sharing rules can impact:

  • SOQL and SOSL queries. A query may return fewer rows than it would operating in system context.
  • DML operations. An operation may fail because the current user doesn’t have the correct permissions. For example, if the user specifies a foreign key value that exists in the organization, but which the current user does not have access to.

So I got back to the controller, changed the “with” to “without” and it fixed the problem. However, all related Profile based security rules should be programmatically implemented. The profile setting on access to the corresponding Visualforce page can be used to do a basic protection on access to this “bulk approve/reject” functionality.

Visualforce expression on null equality comparisons

It is so natural these days to use the following Visualforce code to control the displaying of something wrapped in a pageBlockSectionItem.

<apex:pageBlockSectionItem rendered="{! clazz != null}">
    <apex:outputLabel value="Hellow World!">

Even though the syntax {! clazz != null} looks strange, it is easy to read once getting used to the convention that first exclamation point in the curly brackets means the expression starts from there. Clearly, what the above expression tries to do is to only render the “Hello World!” text when “clazz” property in the related controller is not null. However, this does not work correctly if the Visualforce page is at version earlier than API 26 (Winter ’13 Release). It seems to always return true (at least it always renders the text) in a page with API version earlier than 26. See Winter ’13 release notes Page 202 for more details. I recently spent 2 hours or so debugging a defect that used such expression and only got to resolve it by changing the version number of the Visualforce page. This is the type of defect that cannot be covered by unit testing on controllers only. Manual testing or automated testing like Selenium is necessary.

By the way, the following functions used in Visualforce page to evaluate null property works correctly all the time:

<apex:pageBlockSectionItem rendered="{! NOT(ISNULL(clazz))}">
    <apex:outputLabel value="Hellow World!">

Customization: Conditionally display a message on the standard detail page

Ever wonder dynamically displaying a warning or information message on an object’s detail page without affecting whatever layout is used? Here is a simple requirement: you have an object called MyObject. If you go to view any MyObject record that is created today, you will see an information message “Hello world!” at top of the detail page block. If the record you view is not created today, you will not see the message. The layout of the page should remain not affected including fields and related lists previously configured by drag-and-drop.

JavaScript is an obvious solution as the requirement sounds it only needs manipulations on the browser side. But firstly, how do you inject JavaScript on a standard detail page of an object? Secondly, what if the Created Date of the record is not shown on the page? Now here is a solution: Use controller extension and <apex:detail> Visualforce component. See the following sample code for MyObject controller extension:

public with sharing class MyObjectController {
    private MyObject__c obj;

    public MyObjectController(ApexPages.StandardController controller) {
        this.obj = (MyObject__c) controller.getRecord();

    // Add an INFO message if the record to view is not created today.
    public PageReference checkOnLoad() {
        MyObject__c record = [
                select Id, CreatedDate
                from MyObject__c
                where Id = :this.obj.Id];

        // Not accurate. Just to illustrate the logic
        if (record.CreatedDate != {
            ApexPages.addMessage(new ApexPages.Message(
                    ApexPages.severity.INFO, 'Hello World!'));
        return null;

Note that in the checkOnLoad method of the controller, it queries the record. This is necessary as this method is going to be called before the standard View action of the controller is invoked, which means obj.CreatedDate value is not retrieved so far in this method. The method is specified in the action property of the <apex:page> component. See the following Visualforce page:

<apex:page standardController="MyObject__c" extensions="MyObjectController"
    <apex:pageMessages />
    <apex:detail subject="{!MyObject__c.Id}"/>

The Visualforce component <apex:detail> presents exactly the pre-defined layout used for the record. You can add optional attributes (relatedList, relatedListHover, inlineEdit, etc.) on the tag to specify if the detail page should include related list, related list hover or support inline editing, etc. The <apex:detail> is so neat and so easy to use that a four line code Visalforce page does exactly what you need to do. Moreover, you could now add any powerful JavaScript to this Visualforce page to do all sorts of client side UI changes!

The final step of making it work is to replace the standard View page with the above Visualforce page. Follow these steps: Setup -> Create -> Objects -> MyObject -> Standard Buttons and Links -> Click “Edit” link beside the View -> Override With -> Pick the name of the above Visualforce page -> Save.

Danger: Date value can be assigned to a Datetime variable

Surprisingly in Apex it allows a Date value to be assigned to a Datetime variable without explicit conversion. See an example as follows:

Date d =;
Datetime dt = d;

Firstly, this would cause a timezone problem. Date d is created as today’s date. The debug log shows that Datetime dt is created based on the timezone of the organization the code is running. The time part of Date d is 00:00:00 GMT. So if the timezone of the organization is GMT-5, Datetime dt will have a 19:00:00 yesterday local time. When is called, it returns yesterday’s day of the month. The correct way of converting Date to Datetime avoiding the timezone issue is to use the following statement:

Datetime dt = Datetime.newInstance(d.year(), d.month(),;

Secondly, the fact that “a Date value can be assigned to a Datetime variable” in Apex makes an error to be noticed only at our latest managed package installation time. We had a global method in a class in our managed package (prefix: cve): cve.BusinessHoursUtil.addDays(Datetime, Integer). However a few code references in another managed package (prefix: cvestp) call this method passing in a date value and we have not noticed this for long time as it can compile. We recently added a overloaded method cve.BusinessHoursUtil.addDays(Date, Integer). Since this is the accurate method, the referencing code in cvestp should be now looking for the new method. However the cve version number for the referencing classes are at a much earlier version which does not have the new method. It still passes compilation and we can create our cvestp managed package. It gives out the following “method is not visible” error when the customer installs our cvestp managed package:

Dependent class is invalid and needs recompilation: (cvestp)
cvestp.IncompleteClaimTasks: line 77, column 28: Package Visibility:
Method is not visible: cve.BusinessHoursUtil.addDays(Date, Integer)