How to filter on CreatedDate using a date range in SOQL?

Question

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:

Answer 1

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.