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 Dec 29 '18 at 3:39
David Reed
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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 Dec 29 '18 at 3:39
David Reed
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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
Dec 29 '18 at 3:42
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49
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 Dec 29 '18 at 3:39
David Reed
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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 Dec 29 '18 at 3:39
David Reed
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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 Dec 29 '18 at 3:39
David Reed
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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 Dec 29 '18 at 3:39
David Reed
30.5k61746
edited Dec 29 '18 at 3:39
David Reed
30.5k61746
edited Dec 29 '18 at 3:39
David Reed
30.5k61746
30.5k61746
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
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 Dec 29 '18 at 3:22
Abhishek Sagar
161
asked Dec 29 '18 at 3:22
Abhishek Sagar
161
161
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
Dec 29 '18 at 3:42
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49
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
Dec 29 '18 at 3:42
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49
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
Dec 29 '18 at 3:42
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
Dec 29 '18 at 3:42
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49
add a comment |
2 Answers
2
active
oldest
votes
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 Dec 29 '18 at 8:20
Naval Sharma
1,11429
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 Dec 29 '18 at 5:20
sfdcfox
248k11189424
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'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 Dec 29 '18 at 8:20
Naval Sharma
1,11429
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 Dec 29 '18 at 8:20
Naval Sharma
1,11429
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 Dec 29 '18 at 8:20
Naval Sharma
1,11429
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 Dec 29 '18 at 8:20
Naval Sharma
1,11429
answered Dec 29 '18 at 8:20
Naval Sharma
1,11429
answered Dec 29 '18 at 8:20
Naval Sharma
1,11429
answered Dec 29 '18 at 8:20
Naval Sharma
1,11429
1,11429
add a comment |
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 Dec 29 '18 at 5:20
sfdcfox
248k11189424
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 Dec 29 '18 at 5:20
sfdcfox
248k11189424
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 Dec 29 '18 at 5:20
sfdcfox
248k11189424
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 Dec 29 '18 at 5:20
sfdcfox
248k11189424
answered Dec 29 '18 at 5:20
sfdcfox
248k11189424
answered Dec 29 '18 at 5:20
sfdcfox
248k11189424
answered Dec 29 '18 at 5:20
sfdcfox
248k11189424
248k11189424
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
Dec 29 '18 at 3:42
@abhisekSagar can you post a picture of your query results regarding ActivityDateTime query
– Santanu Boral
Dec 29 '18 at 4:49