SOQL) WHERE 조건절에서의 날짜 사용

1) 날짜 형식을 통한 조건절 사용

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

 

특정한 날짜를 기반해 쿼리 결과를 필터하기 위해서, 날짜 형식을 사용한 값을 세팅하면 된다. 아래의 테이블에서는 SOQL 쿼리의 WHERE 조건 절에서 사용할 수 있는 date와 dateTime 형식들을 보여준다.

 

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

세일즈포스에서는 UTC(Coordinated Universal Time) 타입으로 dateTime을 저장한다. 다른 타임존에서의 dateTime을 제공하기 위해서는 time zone offset을 이용할 수 있다. 

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

 

2) 문자 표현으로 조건절 사용하기

 날짜의 상대적인 범위에 대해 쿼리 결과 조건을 만들 때, 문자로서의 특정 표현을 사용할 수 있다. 예를 들어, 3개월 내의 특정 결과 값을 구해야 하거나 내년 회계년도 이후의 데드라인에 있는 결과 값을 필요로 할때와 같은 경우이다. 이러한 표현들은 현재를 기준으로 한 상대적인 범위를 나타낸다. 

 

 해당 표현들은 =, >, < 등과 같은 비교 수식자들을 사용한다. 아래의 테이블을 통해 확인해보자.

YESTERDAY	Starts at 12:00:00 AM on the day before the current day and continues for 24 hours.	SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
TODAY	Starts at 12:00:00 AM on the current day and continues for 24 hours.	SELECT Id FROM Account WHERE CreatedDate > TODAY
TOMORROW	Starts at 12:00:00 AM. on the day after the current day and continues for 24 hours.	SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW
LAST_WEEK	Starts at 12:00:00 AM on the first day of the week before the current week and continues for seven days.	SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK
THIS_WEEK	Starts at 12:00:00 AM on the first day of the current week and continues for seven days.	SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK
NEXT_WEEK	Starts at 12:00:00 AM on the first day of the week after the current week and continues for seven days.	SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK
LAST_MONTH	Starts at 12:00:00 AM on the first day of the month before the current month and continues for all the days of that month.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH
THIS_MONTH	Starts at 12:00:00 AM on the first day of the current month and continues for all the days of that month.	SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH
NEXT_MONTH	Starts at 12:00:00 AM on the first day of the month after the current month and continues for all the days of that month.	SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH
LAST_90_DAYS	Starts at 12:00:00 AM 90 days before the current day and continues up to the current second. (The range includes today. Using this date value includes records from 91 days ago up to the current day.)	SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
NEXT_90_DAYS	Starts at 12:00:00 AM on the day that the report is run and continues for 90 days. (The range includes today.)	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS
LAST_N_DAYS:n	Starts at 12:00:00 AM n days before the current day and continues up to the current second. (The range includes today. Using this date value includes records from n + 1 days ago up to the current day.) In standard filters, n can be 7, 30, 60, 90, or 120.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
NEXT_N_DAYS:n	For standard date filters, starts at 12:00:00 AM on the day that the report is run and continues for n days. (The range includes today.) In standard filters, n can be 7, 30, 60, 90, or 120.For custom field filters, starts at 12:00:00 AM on the next day and continues for the next n days. (The range doesn’t include today.)	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
N_DAYS_AGO:n	Starts at 12:00:00 AM on the day n days before the current day and continues for 24 hours. (The range doesn’t include today.)	SELECT Id FROM Opportunity WHERE CloseDate = N_DAYS_AGO:25
NEXT_N_WEEKS:n	Starts at 12:00:00 AM on the first day of the week after the current week and continues for n times seven days.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4
LAST_N_WEEKS:n	Starts at 12:00:00 AM on the first day of the week that started n weeks before the current week, and continues up to 11:59 PM on the last day of the week before the current week.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
N_WEEKS_AGO:n	Starts at 12:00:00 AM on the first day of the week that started n weeks before the start of the current week and continues for seven days.	SELECT Id FROM Opportunity WHERE CloseDate = N_WEEKS_AGO:3
NEXT_N_MONTHS:n	Starts at 12:00:00 AM on the first day of the month after the current month and continues until the end of the nth month.	SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2
LAST_N_MONTHS:n	Starts at 12:00:00 AM on the first day of the month that started n months before the current month and continues up to 11:59 PM on the last day of the month before the current month.	SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
N_MONTHS_AGO:n	Starts at 12:00:00 AM on the first day of the month that started n months before the start of 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 at 12:00:00 AM on the first day of the current calendar quarter and continues to the end of the quarter.	SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER
LAST_QUARTER	Starts at 12:00:00 AM on the first day of the calendar quarter before the current calendar quarter and continues to the end of that quarter.	SELECT Id FROM Account WHERE CreatedDate = LAST_QUARTER
NEXT_QUARTER	Starts at 12:00:00 AM on the first day of the calendar quarter after the current calendar quarter and continues to the end of that quarter.	SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER
NEXT_N_QUARTERS:n	Starts at 12:00:00 AM on the first day of the calendar quarter after the current quarter and continues to the end of the calendar quarter n quarters in the future. (The range doesn’t include the current quarter.)	SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2
LAST_N_QUARTERS:n	Starts at 12:00:00 AM on the first day of the calendar quarter n quarters ago and continues to the end of the calendar quarter before the current quarter. (The range doesn’t include the current quarter.)	SELECT Id FROM Account WHERE CreatedDate = LAST_N_QUARTERS:2
N_QUARTERS_AGO:n	Starts at 12:00:00 AM on the first day of the calendar quarter n quarters before the current calendar quarter and continues to the end of that quarter.	SELECT Id FROM Opportunity WHERE CloseDate = N_QUARTERS_AGO:3
THIS_YEAR	Starts at 12:00:00 AM 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 at 12:00:00 AM on January 1 of the year before the current year and continues through the end of December 31 of that year.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR
NEXT_YEAR	Starts at 12:00:00 AM on January 1 of the year after the current 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 at 12:00:00 AM on January 1 of the year after the current 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 at 12:00:00 am on January 1, n+1 years ago. The range ends on December 31 of the year before the current year.	SELECT Id FROM Opportunity WHERE CloseDate = LAST_N_YEARS:5
N_YEARS_AGO:n	Starts at 12:00:00 AM on January 1 of the calendar year n years before the current calendar 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 at 12:00:00 AM on the first day of the current fiscal quarter and continues through the end of the last day of the current fiscal quarter. The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
LAST_FISCAL_QUARTER	Starts at 12:00:00 AM on the first day of the fiscal quarter before the current fiscal quarter and continues through the last day of that fiscal quarter. The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER
NEXT_FISCAL_QUARTER	Starts at 12:00:00 AM on the first day of the fiscal quarter after the current fiscal quarter and continues through the last day of that fiscal quarter. (The range doesn’t include the current quarter.) The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
NEXT_N_FISCAL_​QUARTERS:n	Starts at 12:00:00 AM on the first day of the fiscal quarter after the current fiscal quarter and continues through the end of the last day of the nth fiscal quarter. (The range doesn’t include the current fiscal quarter.) The fiscal quarter 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 at 12:00:00 AM on the first day of the fiscal quarter n fiscal quarters ago and continues through the end of the last day of the previous fiscal quarter. (The range doesn’t include the current fiscal quarter.) The fiscal quarter 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 at 12:00:00 AM on the first day of the fiscal quarter n fiscal quarters before the current fiscal quarter and continues through the end of the last day of that fiscal quarter.	SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_QUARTERS_AGO:6
THIS_FISCAL_YEAR	Starts at 12:00:00 AM on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
LAST_FISCAL_YEAR	Starts at 12:00:00 AM on the first day of the fiscal year before the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
NEXT_FISCAL_YEAR	Starts at 12:00:00 AM on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup.	SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
NEXT_N_FISCAL_​YEARS:n	Starts at 12:00:00 AM on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of the nth fiscal year. (The range doesn’t include the current fiscal year.) The fiscal quarter 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 at 12:00:00 AM on the first day of the fiscal year n fiscal years ago and continues through the end of the last day of the fiscal year before the current fiscal year. (The range doesn’t include the current fiscal year.) The fiscal quarter 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 at 12:00:00 AM on the first day of the fiscal year n fiscal years ago and continues through the end of the last day of that fiscal year.	SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_YEARS_AGO:3

 

FISCAL 표현을 쓸 때는, 세일즈포스에서 커스텀을 반드시 먼저 해야한다. 

• 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.

 

3) 유효한 날짜 범위

날짜는 특정 범위 내에서만 유효하다.

1700년 1월 1일 부터 4000년 12월 31일 까지 가능하다. 타임존에 따라 다른데, 예를 들어 the Pacific time zone 같은 경우에는, 1699-12-31T16:00:00 (4:00 PM on December 31, 1699) 로 생각 하면 된다.