Salesforce Date 형 WHERE 절 조건 처리 방법

 

You can specify date values or date literals in WHERE clauses to filter SOQL query results. Dates represent a specific day or time, while date literals represent a relative range of time, such as last month, this week, or next year.

This page describes the date formats and date literals for SOQL queries that you send to Salesforce. For information on formatting the dates and times returned by Salesforce, see FORMAT () and Converting Time Zones in Date Functions.

Filter Query Results Using Dates

The fieldExpression of a WHERE clause supports filtering query results based on date and dateTime fields. Use comparison operators between a field name and a specified date value to filter for results that match the condition. For example, this query filters for Account records that were created after the specified date and time.

 

 

 

SELECT Id
FROM Account
WHERE CreatedDate > 2005-10-08T01:02:03Z

To filter query results based on a specific date, set a value using a date format. To filter query results based on a specific date and time, set a value using a dateTime format.

This table shows the supported date and dateTime formats that you can use in the WHERE clause of an SOQL query.

Field TypeFormatExample

date	YYYY-MM-DD	1999-01-01
dateTime	YYYY-MM-DDThh:mm:ss+hh:mm YYYY-MM-DDThh:mm:ss-hh:mm YYYY-MM-DDThh:mm:ssZ	1999-01-01T23:01:01+01:00 1999-01-01T23:01:01-08:00 1999-01-01T23:01:01Z

Although dateTime field values are stored in Salesforce as Coordinated Universal Time (UTC), you can use the time zone offset to provide dateTime values in another timezone. The time zone offset is always from UTC.

For more information on dateTime formats and time zone offsets, see:

• http://www.w3.org/TR/xmlschema-2/#isoformats
• http://www.w3.org/TR/NOTE-datetime

Filter Query Results Using Date Literals

You can use a date literal in the fieldExpression of a WHERE clause to filter query results relative to a range of dates. For example, you can filter for query results that were created within the last three months, or you can filter for results with deadlines after the next fiscal year.

Each date literal represents a range of time relative to the current day. The exact start and stop of the range depends on the date literal and the locale of the user sending the query. If the user hasn't set a personal locale, the range depends on the locale of the org. For more information, see Select Your Language, Locale, and Currency in Salesforce Help.

To filter for results within a range, use the = comparison operator. To filter for results on either side of a range, use the > or < comparison operators. The table shows the available list of date literals, the ranges they represent, and examples.

Date LiteralRangeExample

YESTERDAY	Starts 00:00:00 UTC the day before and continues for 24 hours.	SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
TODAY	Starts 00:00:00 UTC of the current day and continues for 24 hours.	SELECT Id FROM Account WHERE CreatedDate > TODAY
TOMORROW	Starts 00:00:00 UTC after the current day and continues for 24 hours.	SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW
LAST_WEEK	Starts 00:00:00 UTC on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week.	SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK
THIS_WEEK	Starts 00:00:00 UTC on the most recent first day of the week on or before the current day and continues for seven full days. Your locale determines the first day of the week.	SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK
NEXT_WEEK	Starts 00:00:00 UTC on the most recent first day of the week after the current day and continues for seven full days. Your locale determines the first day of the week.	SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK
LAST_MONTH	Starts 00:00:00 UTC on the first day of the month before the current day and continues for all the days of that month.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH
THIS_MONTH	Starts 00:00:00 UTC on the first day of the month that the current day is in and continues for all the days of that month.	SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH
NEXT_MONTH	Starts 00:00:00 UTC on the first day of the month after the month that the current day is in and continues for all the days of that month.	SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH
LAST_90_DAYS	Starts with the current day and continues for the past 90 days.This range includes the current day, not just previous days. So it includes 91 days in total.	SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
NEXT_90_DAYS	Starts 00:00:00 UTC of the next day and continues for the next 90 days.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS
LAST_N_DAYS:n	For the number n provided, starts with the current day and continues for the past n days.This range includes the current day, not just previous days. For example, LAST_N_DAYS:1 includes yesterday and today.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
NEXT_N_DAYS:n	For the number n provided, starts 00:00:00 UTC of the next day and continues for the next n days.This range doesn't include the current day. For example, NEXT_N_DAYS:1 is equivalent to TOMORROW.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
N_DAYS_AGO:n	Starts 00:00:00 on the nth day before and continues for 24 hours.	SELECT Id FROM Opportunity WHERE CloseDate = N_DAYS_AGO:25
NEXT_N_WEEKS:n	For the number n provided, starts 00:00:00 UTC of the first day of the next week and continues for the next n weeks.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4
LAST_N_WEEKS:n	For the number n provided, starts 00:00:00 UTC of the last day of the previous week and continues for the past n weeks.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
N_WEEKS_AGO:n	Starts 00:00:00 on the first day of the nth week before the current week, and continues for seven full days. Your locale determines the first day of the week.	SELECT Id FROM Opportunity WHERE CloseDate = N_WEEKS_AGO:3
NEXT_N_MONTHS:n	For the number n provided, starts 00:00:00 UTC of the first day of the next month and continues for the next n months.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2
LAST_N_MONTHS:n	For the number n provided, starts 00:00:00 UTC of the last day of the previous month and continues for the past n months.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
N_MONTHS_AGO:n	Starts 00:00:00 on the first day of the nth month before the current month, and continues for all the days of that month.	SELECT Id FROM Opportunity WHERE CloseDate = N_MONTHS_AGO:6
THIS_QUARTER	Starts 00:00:00 UTC of the current quarter and continues to the end of the current quarter.	SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER
LAST_QUARTER	Starts 00:00:00 UTC of the previous quarter and continues to the end of that quarter.	SELECT Id FROM Account WHERE CreatedDate > LAST_QUARTER
NEXT_QUARTER	Starts 00:00:00 UTC of the next quarter and continues to the end of that quarter.	SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER
NEXT_N_QUARTERS:n	Starts 00:00:00 UTC of the next quarter and continues to the end of the nth quarter.	SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2
LAST_N_QUARTERS:n	Starts 00:00:00 UTC of the previous quarter and continues to the end of the previous nth quarter.	SELECT Id FROM Account WHERE CreatedDate > LAST_N_QUARTERS:2
N_QUARTERS_AGO:n	Starts 00:00:00 of the nth quarter before the current quarter, and continues to the end of that quarter.	SELECT Id FROM Opportunity WHERE CloseDate = N_QUARTERS_AGO:3
THIS_YEAR	Starts 00:00:00 UTC on January 1 of the current year and continues through the end of December 31 of the current year.	SELECT Id FROM Opportunity WHERE CloseDate = THIS_YEAR
LAST_YEAR	Starts 00:00:00 UTC on January 1 of the previous year and continues through the end of December 31 of that year.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR
NEXT_YEAR	Starts 00:00:00 UTC on January 1 of the following year and continues through the end of December 31 of that year.	SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
NEXT_N_YEARS:n	Starts 00:00:00 UTC on January 1 of the following year and continues through the end of December 31 of the nth year.	SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_YEARS:5
LAST_N_YEARS:n	Starts 00:00:00 UTC on January 1 of the previous year and continues through the end of December 31 of the previous nth year.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_YEARS:5
N_YEARS_AGO:n	Starts 00:00:00 on January 1 of the nth year before the current year, and continues through the end of December 31 of that year.	SELECT Id FROM Opportunity WHERE CloseDate = N_YEARS_AGO:2
THIS_FISCAL_QUARTER	Starts 00:00:00 UTC on the first day of the current fiscal quarter and continues through the end of the last day of the fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
LAST_FISCAL_QUARTER	Starts 00:00:00 UTC on the first day of the last fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER
NEXT_FISCAL_QUARTER	Starts 00:00:00 UTC on the first day of the next fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
NEXT_N_FISCAL_​QUARTERS:n	Starts 00:00:00 UTC on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate < NEXT_N_FISCAL_QUARTERS:6
LAST_N_FISCAL_​QUARTERS:n	Starts 00:00:00 UTC on the first day of the last fiscal quarter and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6
N_FISCAL_QUARTERS_AGO:n	Starts 00:00:00 on the first day of the nth fiscal quarter before the current one, and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_QUARTERS_AGO:6
THIS_FISCAL_YEAR	Starts 00:00:00 UTC on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
LAST_FISCAL_YEAR	Starts 00:00:00 UTC on the first day of the last fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
NEXT_FISCAL_YEAR	Starts 00:00:00 UTC on the first day of the next fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
NEXT_N_FISCAL_​YEARS:n	Starts 00:00:00 UTC on the first day of the next fiscal year and continues through the end of the last day of the nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_FISCAL_YEARS:3
LAST_N_FISCAL_​YEARS:n	Starts 00:00:00 UTC on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_FISCAL_YEARS:3
N_FISCAL_YEARS_AGO:n	Starts 00:00:00 on the first day of the nth fiscal year before the current fiscal year, and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_YEARS_AGO:3

To use FISCAL date literals, you must first define custom fiscal years in Salesforce. If you use FISCAL date literals and specify a time range outside of a defined fiscal year, an invalid date error is returned.

Whether the current day is included in the range of a date literal depends on which date literal you use.

• If the UNIT in LAST_N_UNIT:n is any unit except DAYS, the date literal doesn't include the current day. So, for example, LAST_N_WEEKS:1 doesn't include today.
• The two date literals LAST_N_DAYS:n and LAST_90_DAYS do include the current day. So, for example, LAST_N_DAYS:1 includes yesterday and today. And LAST_90_DAYS includes 91 days, not just 90.

Minimum and Maximum Dates

Only dates within a certain range are valid. The earliest valid date is 1700-01-01T00:00:00Z GMT, or just after midnight on January 1, 1700. The latest valid date is 4000-12-31T00:00:00Z GMT, or just after midnight on December 31, 4000. These values are offset by your time zone. For example, in the Pacific time zone, the earliest valid date is 1699-12-31T16:00:00, or 4:00 PM on December 31, 1699.