4

I'm building dynamic SOQL query for the search page

I have the following code:

VFP:

<apex:column headerValue="Invoice Date">
<apex:outputText value="{0,date,MMMM' 'dd', 'yyyy}" label="date"> 
      <apex:param value="{!p.Invoice_Date__c}"  />
</apex:outputText> 
</apex:column>

Apex:

My Invoice_Date__c is a date field in the object

If I execute the following code in Anonymous window I do see the correct format but with my building dynamic SOQL I see the 2017-07-17 00:00:00

Date d = s.Invoice_Date__c;
string dateStr = DateTime.newInstance(d.year(),d.month(),d.day()).format('yyyy-MM-dd');
system.debug('//' + dateStr);   

Dynamic query building:

if (s.Invoice_Date__c != null) {
      Datetime d = s.Invoice_Date__c;
      String dateOutput = d.format('yyyy-MM-dd');
      string invoiceDateFormated = DateTime.newInstance(d.year(),d.month(),d.day()+1).format('yyyy-MM-dd');  
      system.debug('dateOutput: ' + dateOutput);                                       
      query += ' and Invoice_Date__c = '+invoiceDateFormated ; 
}

other thing I have noticed that even if I select today's date which is 07/17/2017 but in debug it showing me 07/16/2017 that's why I end-up adding +1

2 Answers 2

10

You didn't do the same thing in both examples:

String foo(Date input)
{ // your first example
    return DateTime.newInstance(
        input.year(), input.month(), input.day()
    ).format('yyyy-MM-dd');
}
String bar(Date input)
{ // your second example
    Datetime output = input;
    // Type Coercion  ^^^^^
    return output.format('yyyy-MM-dd');
}
String baz(Date input)
{ // another approach
    return DateTime.newInstance(
        input, Time.newInstance(0,0,0,0)
    ).format('yyyy-MM-dd');
}

system.debug(foo(Date.today()));
system.debug(bar(Date.today()));
system.debug(baz(Date.today()));

In your first example, you use foo, but in your second, you use bar. What you do in bar is called Type Coercion where you assign a Date to a Datetime. That gives it a Time instance of (0,0,0,0), but a Time Zone of GMT. So you could also fix bar by using formatGmt:

String qux(Date input)
{ // your second example rewritten
    Datetime output = input;
    return output.formatGmt('yyyy-MM-dd');
    //                  ^^^
}

You could also think about your original bar as being equivalent to a slight change in baz:

String quux(Date input)
{
    Datetime output = Datetime.newInstanceGmt(input, Time.newInstance(0,0,0,0));
    // now output is the same as it was in `bar`
    return output.format('yyyy-MM-dd');
}

You have the Datetime in the GMT Timezone, so you need to use formatGmt:

String garply(Date input)
{
    Datetime output = Datetime.newInstanceGmt(input, Time.newInstance(0,0,0,0));
    // now output is the same as it was in `bar`
    return output.formatGmt('yyyy-MM-dd');
    //                  ^^^
}

* What's after baz?

0
6

To format a Date object to YYYY-MM-DD, you just need one line:

String.valueOf(dateObject);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.