[SalesForce] How to filter on CreatedDate using a date range in SOQL

I'm having problems trying to filter a query by CreatedDate using a date range. The query returns always a wrong set of records.

For example, if I want to get all records created until 2020-07-28, it only returns records created until 2020-07-27 (a day before the actual limit), so I have to add one day to the real date I want, and it works but obviously it is not accurate at all.

This is the query I do:

List<ContentVersion> contVersionList = 
[SELECT Id, Title, Description 
FROM ContentVersion 
WHERE IsLatest = true AND CreatedDate >= :dateFrom AND CreatedDate <= :dateUntil]';

The format of the dates that are being inserted into the query are (as an example) in this format by default:

dateFrom: 2020-07-26T00:00:00Z

dateUntil: 2020-07-30T00:00:00Z

The problem is that I don't know how to create a Date or Datetime, from 1:00:00 of the dateFrom, to 23:59:59 of the dateUntil

I have tried by converting to string, concatenating the hour, and then converting the string to date again. I. e:

Date dateUntil = Date.valueOf('2020-07-30' + 'T23:59:59Z')

But it doesn't work because it ignores the hour that I pass, and set it to T00:00:00Z again.

I've searched a lot how to do this simple thing but I haven't able to do it. If you guys know how to do it I would be glad to get some advice.

Thanks very much and regards.

These are my timezone settings in my org:

Timezone settings in my org

Best Answer

Date values are always interpreted as if they were from midnight in GMT or local time zone (depending on the method you use). Use a DateTime variable instead.

DateTime dateUntil = DateTime.parse('7/30/2020, 12:59 PM');

The exact format will depend on your locale; check the documentation for more information.

Or, you can use the newInstance method:

DateTime dateUntil = DateTime.newInstance(2020, 7, 30, 23, 59, 59);

There's a lot of ways to do this correctly, but you'll need to check the documentation. The number of possible options makes it really easy to do it incorrectly.

Related Topic