SOQL Date and DateTime Filtering: Literals, Ranges, and Practical Patterns
Date filtering is central to reports, integrations, incremental loads, and operational troubleshooting.
Relative Date Literals
For business-relative windows, SOQL date literals keep queries readable:
SELECT Id, Name, CreatedDate
FROM Account
WHERE CreatedDate = TODAY
Other useful literals include YESTERDAY, THIS_WEEK, LAST_WEEK, THIS_MONTH, THIS_YEAR, and parameterized forms such as LAST_N_DAYS:n.
SELECT Id, Subject, CreatedDate
FROM Case
WHERE CreatedDate = LAST_N_DAYS:30
Absolute DateTime Range
For deterministic integration checkpoints, explicit timestamps are often better:
SELECT Id, LastModifiedDate
FROM Account
WHERE LastModifiedDate >= 2026-08-23T00:00:00Z
AND LastModifiedDate < 2026-08-24T00:00:00Z
A half-open range (>= start and < end) avoids awkward end-of-day precision assumptions.
Date vs DateTime
Match the value to the field type. CloseDate is a Date; CreatedDate, SystemModstamp, and LastModifiedDate are DateTime fields.
SELECT Id, Name, CloseDate
FROM Opportunity
WHERE CloseDate = 2026-08-31
Incremental Integration Pattern
For integrations, do not casually equate "records changed recently" with CreatedDate. Choose a field that reflects the change semantics you need.
SELECT Id, Name, SystemModstamp
FROM Account
WHERE SystemModstamp > 2026-08-23T15:00:00Z
ORDER BY SystemModstamp, Id
Persist the checkpoint only after the batch has been processed safely. For high-volume/event-driven synchronization, CDC or Bulk API patterns may be more appropriate than repeatedly polling broad SOQL windows.
Be Explicit About Time Zones
DateTime values represent instants. Use explicit UTC timestamps (Z) for machine-to-machine checkpoints where possible, and avoid building integrations around a server's local timezone assumptions.