17

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?

  • 1
    you could have tried "Date endOfMonth = Date.newInstance(2012,10,01);" and checked for closedate <: endOfMonth – Sathya Nov 27 '12 at 13:41
  • good point, but still seems counterintuitive, since the next person who comes to work on the code it would look like that should return all cases for october 1st – Ralph Callaway Nov 28 '12 at 2:34
  • 2
    If you do it @Sathya's way then you might want to rename the variable endOfMonthDT to beginningOfNextMonth, so it is clearer that the query gets everything from the start of this month, inclusive, to the beginning of next month, exclusive. Note that less than, not less than or equal to, is used. – Peter Knolle Nov 28 '12 at 3:31
  • @PeterKnolle good point – Ralph Callaway Nov 28 '12 at 21:22
12

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];
  • 1
    glad to sharing what we have with the community :) – highfive Jul 17 '14 at 4:50
12

You'll want to check out the Date Literals that were added a few releases ago. They start at midnight of each range, and have things like LAST_N_DAYS, LAST_QUARTER, etc.

The only gotcha is it takes a little to get used to operators for ranges; = means "in", < means "before the beginning of", and > means "after the end of".

  • That'll definitely work, for last and current month, but what about an arbitrary month? – Ralph Callaway Nov 28 '12 at 2:44
  • Yeah not so much for arbitrary months, but your posted solutions for date queries cover those as well. Date literals are nice but don't solve all problems. – jkraybill Dec 3 '12 at 3:07
1

It is worth noting that Apex performs auto-conversion between Date and DateTime.

Date first = Date.newInstance(2018, 03, 19);
DateTime firstDT = first; // this is 2018/03/19 00:00:00.0
DateTime secondDT = DateTime.newInstance(2018, 03, 19, 13, 37, 06);
Date second = secondDT; // this is 2018/03/19

This happens for assignments, like above, and when passing parameters (because we are effectively assigning a value to a parameter variable anyway). Explicit casts can also be used though are unnecessary.

BTW, I would suggest that the first example in Ralph Callaway's response isn't quite right. I think this should be changed as follows:

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

The change is from "closedDate <= :endOfMonth" to "closedDate < :startOfNextMonth". We don't want an entry falling at exactly midnight at the start of the next month being matched, hence the "<" instead of "<=".

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.