How to use BUILTIN.PERIOD

Question

I want to query the last seven day's worth of invoices. This is my best attempt:

SELECT T.* 
FROM Transaction AS T
WHERE T.type IN('CustInvc') AND trandate IN(BUILTIN.PERIOD('SDLW', 'START', 'ALL', '>'))

---

I've read the documentation, but I'm missing something. I believe SDLW stands for SAME_DAY_LAST_WEEK. I even tried (but couldn't) sign up for an account to view this answer. I've clipped the relevant documentation below as HTML.

body {
  font-family: 'Oracle Sans', -apple-system, BlinkMacSystemFont, "Segoe UI", "Helvetica Neue", Arial, sans-serif;
}

<tr>
  <td style="padding:3.6px;width:17.5%;">
    <p style="font-size:1em !important;margin:0px;">
      <code>PERIOD</code>
    </p>
  </td>
  <td style="padding:3.6px;width:23.4%;">
    <p style="font-size:1em !important;margin:0px;">Returns the contents of the IN predicate for a relative date range as a subselection</p>
  </td>
  <td style="padding:3.6px;width:36.5%;">
    <ul>
      <li>
        <p style="font-size:1em !important;margin:0px;">Range ID</p>
        <p style="font-size:1em !important;margin:0px;">This parameter accepts the same property values listed in the <a href="section_1544111773.html" class="o-hf">query.RelativeDateRange</a> enum in the N/query module (for example, <code>'LFY'</code> to represent a range starting or ending last fiscal
          year).
        </p>
      </li>
      <li>
        <p style="font-size:1em !important;margin:0px;">Range type</p>
        <p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
        <ul>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'START'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'END'</code>
            </p>
          </li>
        </ul>
      </li>
      <li>
        <p style="font-size:1em !important;margin:0px;">Adjustment</p>
        <p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
        <ul>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'NOT_LAST'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'ALL'</code>
            </p>
          </li>
        </ul>
      </li>
      <li>
        <p style="font-size:1em !important;margin:0px;">Operator</p>
        <p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
        <ul>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'BETWEEN'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'NOT BETWEEN'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'&lt;'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'&lt;='</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'&gt;'</code>
            </p>
          </li>
          <li>
            <p style="font-size:1em !important;margin:0px;">
              <code>'&gt;='</code>
            </p>
          </li>
        </ul>
      </li>
    </ul>
  </td>
  <td style="padding:3.6px;width:22.4%;">
    <pre width="370.7472" class="line-numbers precomplete" tabindex="0">                    <code class="language-sql hljs">BUILTIN.PERIOD('LFY', '<span class="hljs-operator"><span class="hljs-keyword">END</span><span class="hljs-string">', '</span>NOT_LAST<span class="hljs-string">', '</span>&gt;<span class="hljs-string">') 
</span></span></code>
                  </pre>
    <pre width="376.0704" class="line-numbers precomplete" tabindex="0">                    <code class="language-sql hljs">BUILTIN.PERIOD('LFY', '<span class="hljs-operator"><span class="hljs-keyword">START</span><span class="hljs-string">', '</span><span class="hljs-keyword">ALL</span><span class="hljs-string">', '</span><span class="hljs-keyword">BETWEEN</span><span class="hljs-string">') 
</span></span></code>
                  </pre>
  </td>
</tr>

Answer 1

Use RELATIVE_RANGES together with the > operator

SELECT * FROM transaction
WHERE type IN('CustInvc')
AND trandate > (BUILTIN.RELATIVE_RANGES('SDLW', 'START'))

Or if you want to have the same day last week included use the >= operator.

SELECT * FROM transaction
WHERE type IN('CustInvc')
AND trandate >= (BUILTIN.RELATIVE_RANGES('SDLW', 'START'))