0

I need help in designing a SOQL QUERY.

Scenario is: Every day 10 pm my batch class will run (Its a scheduled one) and in the batch class i have a query to fetch all the orders which are stuck at 'Ready To Submit' status.Now i need to add 2 more condition to my SOQL:

1- I need only today's orders 2- Which are created before 7pm.

The format of CreatedDate in our salesforce org looke like: 2023-07-21T03:17:43.000+0000 (yyyy-MM-dd'T'HH:mm:ss.SSS)

yyyy is the four-digit year MM is the two-digit month (01-12) dd is the two-digit day (01-31) HH is the two-digit hour (00-23) mm is the two-digit minute (00-59) ss is the two-digit seconds (00-59) SSS is the three-digit milliseconds (000-999)

I have formed the query like below- but i am getting an error - Unknown error parsing query.

SELECT Id,OrderStatus__c ,CreatedDate from Order where 
OrderStatus__c = 'Ready To Submit' AND 
CreatedDate = TODAY AND 
AND TIMEVALUE(CreatedDate) < 19:00:00.000+0000

I am getting Unknown error parsing query. Please advise me, on the my SOQL query.

Improve this question
7
  • 1
    are you performing a direct SOQL query in query editor, or are calling the query via an Apex class?
    – prem22
    Commented Jul 21, 2023 at 9:15
  • @prem22 I am trying now in query editor.
    – Archita Patnaik
    Commented Jul 21, 2023 at 9:20
  • 1
    I am sorry, you have mentioned, that you are going to perform it in the batch class.
    – prem22
    Commented Jul 21, 2023 at 9:28
  • That's true. I am just trying to see how the query works by putting in to a query editor first, just for testing purpose.
    – Archita Patnaik
    Commented Jul 21, 2023 at 10:01
  • I see, I don't think you can use TIMEVALUE(CreatedDate) in SOQL query editor, that's why I was wondering initially, in case you are going to use the query in an apex class, then there will be way to achieve the query you are trying to achieve. Also just to confirm, your CreatedDate is really in this format 'yyyy-MM-dd'T'HH:mm:ss.SSS'?
    – prem22
    Commented Jul 21, 2023 at 10:07

1 Answer 1

Reset to default
1

Unfortunately you can't do TIMEVALUE in a SOQL query. That function is only available in formula fields and validation rules.

In Apex, you could do the following:

Date today = Date.today();
Datetime sevenPmToday = Datetime.newInstance(today.year(), today.month(), today.day(), 19, 0, 0);
List<Order> ordersTodayBeforeSevenPm = [SELECT Id, OrderStatus__c, CreatedDate FROM Order WHERE OrderStatus__c = 'Ready To Submit' AND CreatedDate = TODAY AND CreatedDate < :sevenPmToday];
  • today is today's date
  • sevenPmToday is a new Datetime which is constructed using today and 7pm
  • that can then be used in a SOQL query

To test the time in the Query Editor, your time format needs to be one of these:

  • YYYY-MM-DDThh:mm:ss+hh:mm
  • YYYY-MM-DDThh:mm:ss-hh:mm
  • YYYY-MM-DDThh:mm:ssZ

So to test today (21st July 2023), you would do:

SELECT Id, OrderStatus__c, CreatedDate FROM Order WHERE OrderStatus__c = 'Ready To Submit' AND CreatedDate = TODAY AND CreatedDate < 2023-07-21T19:00:00Z

(Adjust based on your timezone)

Improve this answer
5
  • DateTime currentTime = System.now(); system.debug('currentTime is:'+ currentTime); // "2023-07-21T10:35:13.887Z" Date today = Date.today(); system.debug('Todays date' + today); // "2023-07-21T00:00:00.000Z" Datetime sevenPmToday = Datetime.newInstance(today.year(), today.month(), today.day(), 19, 0, 0); system.debug('sevenPmToday' + sevenPmToday); // "2023-07-21T09:00:00.000Z"
    – Archita Patnaik
    Commented Jul 21, 2023 at 10:47
  • why sevenPmToday time value is coming as 2023-07-21T09:00:00.000Z ? Default Time Zone is set in my org is (GMT+10:00) Australian Eastern Standard Time (Australia/Sydney)
    – Archita Patnaik
    Commented Jul 21, 2023 at 10:48
  • That is correct. It's showing you the time in UTC/GMT
    – fred
    Commented Jul 21, 2023 at 10:55
  • Thanks @fred I got your point. Thanks for your help.
    – Archita Patnaik
    Commented Jul 23, 2023 at 12:03
  • 1
    Also I understood how timezones works from below posts. sfdcnotes.com/2020/07 developer.salesforce.com/forums/?id=906F000000092v1IAA
    – Archita Patnaik
    Commented Jul 23, 2023 at 15:19

You must log in to answer this question.

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