Tuesday, December 3, 2013

handling datetime field in a dynamic Query in Apex

We all need to make the Datetime comparisons in SOQL queries. But if the query is dynamic then the datetime comparison gives an error if you just add the datetime variable directly. e,g  if your code looks like 


date d = system.today().addDays(-7);
string query = 'Select ID from Opportunity where CreatedDate > ' +d;


Then you are likely to get a runtime SOQL datetime format error.

To resolve it we need to change the datetime into a format which is SOQL format. Hence I have written a method for the same.
Use the following method to change your datetime into a format which can be directly put into dynamic SOQL and SOSL where clauses. 


 public static String getSOQLDateTime(DateTime dt) {
        if (dt == null) return '';
        String datevalue = String.valueofGmt(dt);
        string [] datetimepair = datevalue.split(' ');
        string datepart = datetimepair[0];
        string timepart = datetimepair.size() > 1 ? datetimepair[1] : '';
        string [] dateparts = datepart.split('-');
        string [] timeparts = timepart == '' ? null : timepart.split(':');
        string hour = (timeparts == null)? '00' : timeparts[0];
        string minute = (timeparts == null)? '00' : timeparts[1];
        string second = (timeparts == null)? '00' : timeparts[2];
        string millisecond = '000';
        string month = dateparts[1];
        string year = dateparts[0];
        string day = dateparts[2];
        string result = year + '-' + month + '-' + day + 'T' + hour + ':'+ minute + ':' + second + '.' + millisecond + 'Z';
        return result;
    }

No comments:

Post a Comment