Tuesday, November 15, 2016

Date Time field using Dynamic query

Use Case
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 
datetime d = system.now().addDays(-7);
string query = 'Select ID from Opportunity where CreatedDate > ' +d;

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

Solution

To resolve it we need to change the datetime into a format which is in SOQL format. Hence this is the written 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. 
Reusable Code
datetime d = system.now().addDays(-7);
string query = 'Select ID from Opportunity where CreatedDate > ' + getSOQLDateTime (d);

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