【Query Builder】Filter: The List of Sharperlight Syntaxes for specifying a date range

When using the Sharperlight Query Builder to create queries, it is common to narrow down data by using date fields as filters. Sharperlight provides syntaxes for specifying special date ranges. I plan to create and periodically update a list of these syntaxes.

I am creating a list with the assumption that you are specifying a date range.
**Assumption**
<INS>The current date is November 16, 2023.</INS>
<INS>The current time is 11:06:50.000.</INS>

# General #
## DATE ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9b9827c0-0335-0cce-6c52-5078cce18255.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 <INS>00:00:00.000</INS>' AND '20231116 <INS>23:59:59.998</INS>'</dd>
</dl>

## DATE now ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3f95e1ae-62d8-08f5-d2e2-b91e5d5e63c2.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date and Current Time</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 <INS>11:06:50.000</INS>' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE -1y ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/335445e3-96b2-ec54-7a4a-84fa6a9d8d5a.png)
<dl>
  <dt>FROM</dt>
  <dd>One year before the current date</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>2022</INS>1116 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE -1m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fd671252-6d2d-9db3-1cdc-337d9f9a0e21.png)
<dl>
  <dt>FROM</dt>
  <dd>One month before the current date</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '2023<INS>10</INS>16 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE -1d ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/86e21af7-bef6-5ff2-4225-7a455e28d5e8.png)
<dl>
  <dt>FROM</dt>
  <dd>A day before the current date</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '202311<INS>15</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE -1y -1m -1d ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/781a138f-e1b4-3bd7-fed3-88454bd0a7ff.png)
<dl>
  <dt>FROM</dt>
  <dd>A day, one month and one year before the current date</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20221015</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE 1y ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c9a7977f-3e3a-8904-c0cd-5b24072bedd1.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</dd>
  <dt>TO</dt>
  <dd>One year ahead from the current date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '<INS>2024</INS>1116 23:59:59.998'</dd>
</dl>

## DATE 1m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/213acdd0-b928-6371-4092-5d67714ca402.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</dd>
  <dt>TO</dt>
  <dd>One month ahead from the current date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN 'BETWEEN '20231116 00:00:00.000' AND '2023<INS>12</INS>16 23:59:59.998'</dd>
</dl>

## DATE 1d ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/85eeeac7-fc5b-46e6-c0f1-cb4f264f2a00.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</dd>
  <dt>TO</dt>
  <dd>A day ahead from the current date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '202311<INS>17</INS> 23:59:59.998'</dd>
</dl>

## DATE 1y 1m 1d ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2b146820-fe1b-ba12-7f14-9667b093a536.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</dd>
  <dt>TO</dt>
  <dd>A day, one month and one year ahead from the current date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '<INS>20241217</INS> 23:59:59.998'</dd>
</dl>

# Year #
## DATE oys ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7cc12828-dc8c-12b9-6c56-ac5980601914.png)
<dl>
  <dt>FROM</dt>
  <dd><INS>Start of the year</INS> based on the current date (Start of Year - <INS>Before Offsets</INS>)</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20230101</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE oys -11m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c0eb9280-d7d3-65e9-a661-cca5e55f62d8.png)
<dl>
  <dt>FROM</dt>
  <dd>Get <INS>Start of the year</INS> based on the current date first、then get the date <INS>11 months before</INS> the date got</INS></dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20220201</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE ays ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b24b0172-68f0-978c-c52f-57df26f6587a.png)
<dl>
  <dt>FROM</dt>
  <dd><INS>Start of the year</INS> based on the current date (Start of Year - <INS>After Offsets</INS>)</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20230101</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE ays -11m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b7239cdf-3a4e-52e6-d9ad-d9a1cd46db45.png)
<dl>
  <dt>FROM</dt>
  <dd>Get the date <INS>11 months before</INS> the current date first、then get <INS>the start of the year</INS> for the date got</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20220101</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE oye ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/915e1de0-a494-e6d7-a399-32ccece61517.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</INS></dd>
  <dt>TO</dt>
  <dd><INS>End of the year</INS> based on the current date (End of Year - <INS>Before Offsets</INS>)</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '<INS>20231231</INS> 23:59:59.998'</dd>
</dl>

## DATE oye 2m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a2c3fd6a-209c-aad3-6e89-6358b75dfc6b.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</INS></dd>
  <dt>TO</dt>
  <dd>Get <INS>End of the year</INS> based on the current date first、then get the date <INS>2 months ahead</INS> from the date got</INS></dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '<INS>20240229</INS> 23:59:59.998'</dd>
</dl>

## DATE aye ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/27eed7eb-cdb7-6d09-1a32-5df0cd2418f5.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</INS></dd>
  <dt>TO</dt>
  <dd><INS>End of the year</INS> based on the current date (End of Year - <INS>After Offsets</INS>)</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND <INS>'20231231</INS> 23:59:59.998'</dd>
</dl>

## DATE aye 2m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1914e1a5-0113-0fed-0ea2-df43ffcab90b.png)
<dl>
  <dt>FROM</dt>
  <dd>Current Date</INS></dd>
  <dt>TO</dt>
  <dd>Get <INS>2 months ahead</INS> first、then get <INS>the date the end of the year</INS> based on the date got</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '20231116 00:00:00.000' AND '<INS>20241231</INS> 23:59:59.998'</dd>
</dl>

# Month #
## DATE oms ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e78ec9b4-1d9d-1dee-7bb7-653ca0666ab7.png)
<dl>
  <dt>FROM</dt>
  <dd><INS>Start of the month</INS> based on the current date (Start of Month - <INS>Before Offset</INS>)</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20231101</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE oms -2m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c04fc312-8a69-cb1e-4367-73961c59be10.png)
<dl>
  <dt>FROM</dt>
  <dd>Get <INS>the start of the month based on the current date first</INS>, then get the date <INS>2 months before</INS> the date got</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20230901</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE ams ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/61d1df66-17a1-4af7-c325-70f9d73e7868.png)
<dl>
  <dt>FROM</dt>
  <dd><INS>Start of the month</INS> based on the current date (Start of Month - <INS>After Offset</INS>)</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20231101</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>

## DATE ams -2m ##
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6b145056-553b-6656-2318-3841fe71f9a1.png)
<dl>
  <dt>FROM</dt>
  <dd>Get <INS>2 months before the current date first</INS>, then get <INS>the start of the month</INS> based on the date got</dd>
  <dt>TO</dt>
  <dd>Current Date</dd>
  <dt>Being Generated SQL Statement</dt>
  <dd>BETWEEN '<INS>20230901</INS> 00:00:00.000' AND '20231116 23:59:59.998'</dd>
</dl>