Help with a constant in SOQL (Salesforce)

1

Hi,
I have a SOQL problem and am hoping it can be solved here on the SQL forum :slight_smile:

I have a bunch of queries that need to run on the 1st of every month, and I use the function "GetDate()". If for some reason I need to re-run these queries and it's not the 1st of the month, I have to Hard-code the date. per the two examples below.

1st of Month run:
AND WARRANTY_START_DATE between DATEADD(MONTH,-9,cast(GetDate() as date)) and DATEADD(MONTH,-8,cast(GetDate() as date))

Not first of month:
AND WARRANTY_START_DATE between DATEADD(MONTH,-9,cast('2022-11-01' as date)) and DATEADD(MONTH,-8,cast('2022-11-01' as date))

My issue is that there are a lot of these and having to update the date in each seems like waisted time.
I'd like to 'look' into another table (data extension) and find the date there. So I'll have a new table with 1 column/value. Table: Report_Month, Column: Date = '2022-11-01'. This will be changed monthly.

Update my queries to look at that table to find the date, replacing my "GETDATE()' or Hard-coded date:

Something like:
AND WARRANTY_START_DATE between DATEADD(MONTH,-9,cast(Report_Month.Date as date)) and DATEADD(MONTH,-8,cast(Report_Month.Date as date))

Ideas of how I'd accomplish this? SOQL, not SQL.
Thanks.

2

You don't want to use BETWEEN for date/times - as that can lead to missing data at the end of the range. If the column WARRANTY_START_DATE is a date data type then you can use BETWEEN but then you have to remember to use a different pattern depending on date vs datetime.

The easier method is to use an open-interval range, and you can easily calculate the first of the month:

WHERE WARRANTY_START_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 9, 0)
  AND WARRANTY_START_DATE <  DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 8, 0)

This would be done in SQL - not SOQL (which I am not familiar with). However, a quick search for SOQL I found this:

WHERE WARRANTY_START_DATE = N_MONTHS_AGO:6

Which apparently pulls the data for the month 6 months ago.

3

Thanks Jeff,
Your code below actually worked in SOQL:

Could you explain a little more why using "BETWEEN" is a bad idea?

Also, I still need my original issue resolved. I need to point to a "data extension" to get a date value.
Thanks.

4

The formula is all you need. You dont need to look anything up.

If you cast getdate() as a date, it ends up with the time portion set to midnight. Using between would then be something like: datecol between '2022-10-01 00:00:00.000' and '2022-10-31 00:00:00.000' which excludes all times after midnight. If you use the next day, then you are including midnight because between is inclusive

5

Thanks for the explanation.

And thanks for the formula! I get it now, it will automatically be the first of the month, brilliant!

Thanks for your help!

6

One more thing please,
How would I make the previous month dynamic?

Current Code:
Report_Month_Start_HARD = DATEADD(DAY,-31,cast('2022-11-01' as date)),
Report_Month_End_HARD = DATEADD(DAY,-1,cast('2022-11-01' as date))

That gives me a range of Saturday, October 01, 2022 12:00 AM to Monday, October 31, 2022 12:00 AM. (Prior month from current month.)

I tried the below
Report_Month_Start = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 31, 0),
Report_Month_End = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0),

But that gave me a 31 day window around today:
Thursday, October 13, 2022 12:00 AM to Saturday, November 12, 2022 12:00 AM

EDIT: I realize I could just use the same as I did before, with only -1...
WHERE WARRANTY_START_DATE >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
AND WARRANTY_START_DATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
Thanks,

7

That is the answer - since we don't know how many days are in the previous month it is much easier to calculate the first of the previous month and the first of the current month. Then get everything greater than or equal to the first of the previous month - and less than the first of the current month.