1

I want to query people that are born within the last 15 years. This works as expected when a fixed date is used, e.g.

SELECT ID, Name, Birthdate FROM Contact
WHERE Birthdate != null
AND Birthdate > 2006-11-20
ORDER BY Birthdate DESC

However, I cannot get the same result using a SOQL date literal in workbench, no matter which operator is used:

SELECT ID, Name, Birthdate FROM Contact
WHERE Birthdate != null
AND Birthdate > LAST_N_DAYS:5475

It's very strange to see that operators like > or < or even != yield the same result.
Also, LAST_N_YEARS:15 and a smaller N does not do the trick. What's wrong here?


While I understand that the SOQL date literals at hand represented date ranges I cannot rationalize the result returned by Birthdate = LAST_N_DAYS:5475

I get records with birthdates ranging from 1924 to 2021. I have a feeling something else is at play here and specific to the birthday field - since the suggest query works with other Date/Time fields.

2
  • Any error? Also why would you use Date Literals at all for a single date so long ago?
    – Adrian Larson
    Nov 18, 2021 at 2:07
  • @AdrianLarson There is no error, but it's not the expected data. The WHERE-clause is intended as a filter on an Informatica source to exclude contacts that are not eligible.
    – wp78de
    Nov 18, 2021 at 2:12

1 Answer 1

4

Date literals are already a time range. Your date literal query should be written as:

SELECT ID, Name, Birthdate 
FROM Contact
WHERE Birthdate = LAST_N_DAYS:5475
  AND Birthdate != null

Which, in written as normal SQL would read something like:

SELECT * FROM Contacts WHERE CreatedDate BETWEEN 2006-11-17 AND 2021-11-17

In other words, you normally want to use = to find values within the specified date literal range.

It looks "weird" if you've never done this before, but with some experience, you'll start to figure this out mentally.

To assist with this, I think it's worth having a list and description of what it means, for which we'll use as examples for TODAY, assuming today's date was 2021-11-17 (which it is, at the time of this answer for my time zone).

Note that "null" values are considered "before" any time period, so they can show up unexpectedly in some types of queries.

Operator Meaning Start End
= The literal range. 2021-11-17 00:00:00 2021-11-17 23:59:59
< Earlier than the start time for the literal. 0000-01-01 00:00:00 2021-11-16 23:59:59
<= Earlier than the end time for the literal. 0000-01-01 00:00:00 2021-11-17 23:59:59
> Later than the end time for the literal. 2021-11-18 00:00:00 9999-12-31 23:59:59
>= Later than the start time for the literal. 2021-11-17 00:00:00 9999-12-31 23:59:59
!= Before and after the time for the literal. 0000-01-01 00:00:00 2021-11-16 23:59:59
2021-11-18 00:00:00 9999-12-31 23:59:59

Keep in mind that date literals are based on the user's configured time zone, so you may need to adjust for GMT.

Edit:

BirthDate, however, has an additional wrinkle. Date literals behave differently for this field, because it has a special significance.

For example, to find everyone born on this day, you can say:

SELECT Id FROM Contact WHERE BirthDate = TODAY

And for this month:

SELECT Id FROM Contact WHERE BirthDate = THIS_MONTH

Most fields don't behave this way, but this one does.

That means you might need to use date functions to work around this:

SELECT Id FROM Contact WHERE CALENDAR_YEAR(BirthDate) >= 2006 ...

This is also true for list views, so be aware of that limitation. I can't seem to find documentation on this specific behavior, but it does appear to work differently than normal date and date-time fields.

5
  • Thank you, however, this still does not yield the expected result. I get records with birthdates ranging from 1924 (the oldest person) to 2021. That does not make sense, does it? I have a feeling something else is at play here.
    – wp78de
    Nov 18, 2021 at 3:50
  • @wp78de Yeah, I think it's because BirthDate is a "magic" date field; it lets you write queries like BirthDate = THIS_MONTH to find everyone born in November, regardless of the year. I'd forgotten about that. Date literals might not be for you in this case.
    – sfdcfox
    Nov 18, 2021 at 4:04
  • It feels like a year-2000 problem because a query like Birthdate = LAST_N_DAYS:2 gives results from various years (1994, 2003, etc) but with the month/day part in the same range, e.g. 11/15 to 11/17. This is mindboggling. 🤯 I guess a bit too much magic was applied here by Salesforce. :(
    – wp78de
    Nov 18, 2021 at 4:13
  • @wp78de Yeah, it's a really old feature; it was present even when I started in Salesforce, which was in 2005. I don't do a lot with birthdays in Salesforce, but I can see how it makes sense in the general "this is useful for finding people to send them birthday cards" sort of ideal, but not so much for us programmers have to deal with the behavior, especially since it seems to be poorly documented.
    – sfdcfox
    Nov 18, 2021 at 4:15
  • We are getting there. The CALENDAR_YEAR() helps! I wish we could compare the result of CALENDAR_YEAR to a date literal but it's not possible in a WHERE clause (as stated in the documentation). I think I have to settle with this for now. Thank you, again.
    – wp78de
    Nov 18, 2021 at 4:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .