SOQL filter birthdays based on date literal range

Question

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.

Answer 1

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.