4

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!

2 Answers 2

4

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 following

Id Name Test_Time__c
0011N0000xxxxxx test Account 01:00:00.000Z

That 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...

  • You need to use a leading zero for numbers less than 10
  • The milliseconds are optional, and can be given as any arbitrary precision (e.g. 01:00:00.00000000000000000000000000000Z is fine), though Salesforce will only actually store up to 3 decimal places
  • You need to use a timezone indicator at the end of the literal (Z, or +/- 00:00 style, e.g. 01:00:00-06:00)
  • Do not enclose the time literal in quotes
  • Time is mod 24, so 01:00:00Z == 19:00:00-06:00 == 04:00:00+03:00
  • Entered through the web UI, the user's timezone is completely ignored (so even in UTC-6 land, entering the time as 1 am meant it got stored as 01:00:00Z)
2
  • 1
    Date and Time are effectively "timezoneless", essentially being "local" to the reader regardless of timezone, which is why the user timezone is ignored. It is only a Datetime that has a timezone. Unfortunately Salesforce did not accurately apply ISO time formatting and requires the spurious UTC Zulu indicator in the text formatting of the Time literal.
    – Phil W
    Commented Mar 4, 2022 at 8:04
  • Wonderful. Thanks!
    – Dave C
    Commented Mar 4, 2022 at 15:43
4

If you query out Actual_Arrival_Time_D__c from Container__c you can confirm the formatting. When I query a time value I see them in the format 09:45:25.000Z. I have to add the Z to the end of the time to get it to query out correctly in developer console.

Using your field and object names I formed my statement like the examples below.

  • This lets you see the format:

    • Select Actual_Arrival_Time_D__c from Container__c limit 50
  • This fails:

    • Select Actual_Arrival_Time_D__c from Container__c where Actual_Arrival_Time_D__c = 01:00:00.000 limit 50
  • This works:

    • Select Actual_Arrival_Time_D__c from Container__c where Actual_Arrival_Time_D__c = 01:00:00.000Z limit 50'

I hope that helps.

1
  • Thanks for your help!!
    – Dave C
    Commented Mar 4, 2022 at 15:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .