Make Dynamic SOQL Query in Workflow

[adrquint]

Trying to build workflow that will pull some aggregate numbers from Salesforce for a given time period (SchedStartTime).

I have a trigger that will run the workflow every 30 mins and I want to dynamically query salesforce for the current month.

For this current month of July, the WHERE clause should change to:

AND SchedStartTime >= 2024-07-01T00:00:00Z
AND SchedStartTime < 2024-08-01T00:00:00Z

Then the next month in August it would be:

AND SchedStartTime >= 2024-08-01T00:00:00Z
AND SchedStartTime < 2024-09-01T00:00:00Z

[jg80]

I think a little JS would do it. The following gets the start and end of the month:

var firstDay = new Date(new Date().getFullYear(), new Date().getMonth(), 1);
var lastDay = new Date(new Date().getFullYear(), new Date().getMonth() + 1, 0);
let foo = [firstDay, lastDay]
return foo

So in your SQL, you could write:

SELECT
  some_stuff 
FROM
  some_tables
WHERE
  some_criteria = 'Awesome'
  AND schedStartTime >= {{ new Date(new Date().getFullYear(), new Date().getMonth(), 1) }}
  AND schedStartTime  < {{ new Date(new Date().getFullYear(), new Date().getMonth() + 1, 0) }}

Watch out for timezones...