[SalesForce] Apex DateTime formatting for Dynamic SOQL query returning wrong value

Update

Borrowing from @highfive's answer, the easiest way to do this is use the DAY_ONLY operator to convert the DateTime.

SELECT Id FROM Case WHERE DAY_ONLY(ClosedDate) >= :startOfMonth
  AND DAY_ONLY(ClosedDate) <= :endOfMonth

Old Answer

So it seems like there are four options. I'd probably go for the last one, which seems the clearest and simplest. In hindsight this question appears subjective and probably not right for the forum =.

First, you can use @sathya's suggestion which works but might be counter-intuitive if you don't understand the date to datetime conversion that's happening.

Date startOfMonth = Date.newInstance(2012,9,1);
Date endOfMonth = Date.newInstance(2012,10,1);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonth 
        and closedDate <= :endOfMonth];

Second, you can exactly specify the date time window. Which works but feels a little verbose.

DateTime startOfMonthDT = DateTime.newInstance(2012,9,1,0,0,0);
DateTime endOfMonthDT = DateTime.newInstance(2012,9,30,23,59,59);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonthDT 
        and closedDate <= :endOfMonthDT];

Third, if you're only worrying about the current or last month you can use date literals.

List<Case> cases = [
        select id from Case 
        where closedDate = LAST_MONTH];

Finally, you can use the date functions, which seems like the clearest (to me at least)

Integer month = 9;
Integer year = 2012;
List<Case> cases = [
        select id from Case 
        where CALENDAR_MONTH(closedDate) = :month
        and CALENDAR_YEAR(closedDate) = :year];

Attribution

Use:

system.debug(Datetime.now().formatGMT('yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''));

The esteemed Mr Ballinger also recommends the following, but it is wrapped in double quotes and slower, so I would probably not use it, myself.

system.debug(JSON.serialize(Datetime.now()));