Recently I had a strange bug in a project for a report page for closed cases. For whatever reason a case wasn't being included.
Long story short, I was querying for the cases using dates for the start and end of the month, but the last day of the month was being excluded. For example a case with a closedDate of 9/30/12 2:15:12am would not be returned with the following query:
Date startOfMonth = Date.newInstance(2012,9,1);
Date endOfMonth = Date.newInstance(2012,9,30);
List<Case> cases = [
select id from Case
where closedDate >= :startOfMonth
and closedDate <= :endOfMonth];
The issue ended up being that this type of query converted the dates into date times, specifically 9/1/12 12:00:00am and 9/30/12 12:00:00am, which looks good at first, but what we really want to get all cases on the last day is 9/1/12 12:00:00am and 9/30/12 11:59:59pm. I was able to work around by doing this.
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];
That said, regardless of whether this is expected query behavior, it feels like there has to be a safer way to do this date range queries that isn't such a gotcha for developers. Are there any better ways to write the original query without doing the all the extra math to get the exact date time for the first and last second of the month?
Best Answer
Update
Borrowing from @highfive's answer, the easiest way to do this is use the
DAY_ONLY
operator to convert the DateTime.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.
Second, you can exactly specify the date time window. Which works but feels a little verbose.
Third, if you're only worrying about the current or last month you can use date literals.
Finally, you can use the date functions, which seems like the clearest (to me at least)