LAST_N_MONTHS:N in SOQL
Has anyone ever faced an issue with LAST_N_MONTHS:N in SOQL? As per salesforce doc:
For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.
Well the below SOQL works fine as it returns data for last 12 months excluding current month:
SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
But if run the below SOQL, it returns data for last 12 months plus current month
SELECT Id FROM Event WHERE ActivityDateTime = LAST_N_MONTHS:12
Is it a bug?
soql date-literals
edited 4 hours ago
David Reed
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Has anyone ever faced an issue with LAST_N_MONTHS:N in SOQL? As per salesforce doc:
For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.
Well the below SOQL works fine as it returns data for last 12 months excluding current month:
SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
But if run the below SOQL, it returns data for last 12 months plus current month
SELECT Id FROM Event WHERE ActivityDateTime = LAST_N_MONTHS:12
Is it a bug?
soql date-literals
edited 4 hours ago
David Reed
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago
add a comment |
Has anyone ever faced an issue with LAST_N_MONTHS:N in SOQL? As per salesforce doc:
For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.
Well the below SOQL works fine as it returns data for last 12 months excluding current month:
SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
But if run the below SOQL, it returns data for last 12 months plus current month
SELECT Id FROM Event WHERE ActivityDateTime = LAST_N_MONTHS:12
Is it a bug?
soql date-literals
edited 4 hours ago
David Reed
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Has anyone ever faced an issue with LAST_N_MONTHS:N in SOQL? As per salesforce doc:
For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.
Well the below SOQL works fine as it returns data for last 12 months excluding current month:
SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
But if run the below SOQL, it returns data for last 12 months plus current month
SELECT Id FROM Event WHERE ActivityDateTime = LAST_N_MONTHS:12
Is it a bug?
soql date-literals
soql date-literals
edited 4 hours ago
David Reed
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
David Reed
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
David Reed
30k61746
edited 4 hours ago
David Reed
30k61746
edited 4 hours ago
David Reed
30k61746
30k61746
asked 5 hours ago
Abhishek Sagar
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 5 hours ago
Abhishek Sagar
111
asked 5 hours ago
Abhishek Sagar
111
111
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Abhishek Sagar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago
add a comment |
1
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago
1
1
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago
add a comment |
2 Answers
2
active
oldest
votes
It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.
answered 3 hours ago
sfdcfox
247k11188424
add a comment |
It's considering the date/time which is stored in the Database which is always in GMT.
When viewing the record from within the Salesforce UI, the value is
automatically translated to and displayed in the timezone set on your
user record. However, when the data is inserted, updated, or exported
the values will always be in UTC or GMT time and are not translated to
the running user's timezone.
You can try the following query.
SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12
It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.
answered 15 mins ago
Naval Sharma
91028
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "459"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Abhishek Sagar is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f244977%2flast-n-monthsn-in-soql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.
answered 3 hours ago
sfdcfox
247k11188424
add a comment |
It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.
answered 3 hours ago
sfdcfox
247k11188424
add a comment |
It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.
answered 3 hours ago
sfdcfox
247k11188424
It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.
answered 3 hours ago
sfdcfox
247k11188424
answered 3 hours ago
sfdcfox
247k11188424
answered 3 hours ago
sfdcfox
247k11188424
answered 3 hours ago
sfdcfox
247k11188424
247k11188424
add a comment |
add a comment |
It's considering the date/time which is stored in the Database which is always in GMT.
When viewing the record from within the Salesforce UI, the value is
automatically translated to and displayed in the timezone set on your
user record. However, when the data is inserted, updated, or exported
the values will always be in UTC or GMT time and are not translated to
the running user's timezone.
You can try the following query.
SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12
It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.
answered 15 mins ago
Naval Sharma
91028
add a comment |
It's considering the date/time which is stored in the Database which is always in GMT.
When viewing the record from within the Salesforce UI, the value is
automatically translated to and displayed in the timezone set on your
user record. However, when the data is inserted, updated, or exported
the values will always be in UTC or GMT time and are not translated to
the running user's timezone.
You can try the following query.
SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12
It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.
answered 15 mins ago
Naval Sharma
91028
add a comment |
It's considering the date/time which is stored in the Database which is always in GMT.
When viewing the record from within the Salesforce UI, the value is
automatically translated to and displayed in the timezone set on your
user record. However, when the data is inserted, updated, or exported
the values will always be in UTC or GMT time and are not translated to
the running user's timezone.
You can try the following query.
SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12
It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.
answered 15 mins ago
Naval Sharma
91028
It's considering the date/time which is stored in the Database which is always in GMT.
When viewing the record from within the Salesforce UI, the value is
automatically translated to and displayed in the timezone set on your
user record. However, when the data is inserted, updated, or exported
the values will always be in UTC or GMT time and are not translated to
the running user's timezone.
You can try the following query.
SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12
It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.
answered 15 mins ago
Naval Sharma
91028
answered 15 mins ago
Naval Sharma
91028
answered 15 mins ago
Naval Sharma
91028
answered 15 mins ago
Naval Sharma
91028
91028
add a comment |
add a comment |
Abhishek Sagar is a new contributor. Be nice, and check out our Code of Conduct.
Abhishek Sagar is a new contributor. Be nice, and check out our Code of Conduct.
Abhishek Sagar is a new contributor. Be nice, and check out our Code of Conduct.
Abhishek Sagar is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Salesforce Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsalesforce.stackexchange.com%2fquestions%2f244977%2flast-n-monthsn-in-soql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record.
– Sudipta Deb
4 hours ago
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
3 hours ago