SOQL Date Literals

SOQL has a brilliant and time-saving feature for comparing date values, which is called Date Literals. They are fixed string constants used to specify a date range without the need to quote specific dates. They can work with both with Date and Datetime Comparisons.

Say, for instance, you want to find all the patients whose birthday is in the current month. Let’s assume the current Month is August 2019.

So SOQL query can be:

SELECT Id, Name
FROM Contact
WHERE Birthdate >= 2019-08-01
AND Birthdate <= 2019-08-31

This query works, but there is a lot of effort.

  1. You have to determine the current month.
  2. You have to calculate the start date and end date of the month.
  3. You have to check if the month contains 30 or 31 days. And yes, 28, 29 sometimes.

The requirement can be handled very easily by using Date Literals.

The same SOQL query can be rewritten as:

SELECT Id, Birthdate
FROM Contact
WHERE Birthdate = THIS_MONTH

A lot more intuitive query, easier to read, and easier to maintain.

Important
SOQL Date Literals are case insensitive.

Following a list of the most used Date literals, you can find an exhaustive list here.

Date Literal Description Example
YESTERDAY Begins at 00:00 in the previous day, and continues for 24 hours 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = YESTERDAY
TODAY Begins at 00:00 in the current day, and continues for 24 hours 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = TODAY
TOMORROW Begins at 00:00 the next day, and continues for 24 hours 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = TOMORROW
LAST_WEEK Begins at 00:00 on Monday for the previous week, and continues for 7 full days 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = LAST_WEEK
THIS_WEEK Begins at 00:00 on Monday of this week, and continues for 7 full days 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = THIS_WEEK
NEXT_WEEK Begins at 00:00 on Monday for next week, and continues for 7 full days 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = NEXT_WEEK
LAST_MONTH Begins at 00:00 on 1st day of the previous month and continues till the last day of that month 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = LAST_MONTH
THIS_MONTH Begins at 00:00 on 1st day of this month and continues till the last day of this month. 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = THIS_MONTH
NEXT_MONTH Begins at 00:00 on 1st day of the next month and continues till the last day of next month. 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = NEXT_MONTH
THIS_YEAR Begins at 00:00 on 1st day of this Year, and continues till the last day of the year. 
SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = THIS_YEAR
LAST_N_DAYS:n Begins at 00:00 in the current day and continues for n past days. n is a positive Integer variable.

Appointments for the past 3 days

SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = LAST_N_DAYS:3
NEXT_N_DAYS:n Begins at 00:00 the next day, and continues for n future days.n is a positive Integer variable.

Appointments for the next 3 days

SELECT Id
FROM Appointment__c
WHERE Appoitntment_time__c = NEXT_N_DAYS:3