I am building a dynamic SOQL query that may have a TIME field in the where clause:
select Id,Name,Customer__c,Load__c,Actual_Arrival_Date_D__c,Actual_Arrival_Time_D__c,Actual_Departure_Date_L__c,Actual_Departure_Time_L__c,Available_Date_D__c,Available_Time_D__c
from Container__c
where Actual_Arrival_Date_D__c >= 2022-03-02 and
Actual_Arrival_Date_D__c <= 2022-03-04 and
Actual_Arrival_Time_D__c = 01:00:00.000
order by Name limit 1000
In this case Actual_Arrival_Time_D__c is a Time field.
The query pukes with this error:
line 1:329 no viable alternative at character ' ': System.QueryException: Class.ContainerConsoleController.applyContainerFilter: line 154, column 1
How to I format/escape the time literal on the right side of the clause? Thanks!
Best Answer
I haven't been able to find any relevant documentation for this, so it's time to get empirical.
I created a
Test_Time__c
field (field type Time) on Account in my dev edition org, found myself a record in the web UI, and populated this field with a time.Using a plain 'ol query in the developer console (
SELECT Id, Name, Test_Time__c FROM Account
), I got the followingThat gives us a decent idea of what the format should be. It looks like just the time part of a datetime literal, so let's give that a try.
[SELECT Id, Name, Test_Time__c FROM Account WHERE Test_Time__c = 01:00:00.000Z]
Running this query in the dev console query tab works. Copying the body of the query into a string and executing it using
Database.query()
also works.So...