Answers.Body: <p>You can use the <a href="https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day" rel="nofollow noreferrer">LAST_DAY</a> function if you're working with MySQL. To only get entries with a <code>postDate</code> on the last day of the month, you can check if <code>LAST_DAY(postDate)</code> is identical with <code>DATE(postDate)</code>.</p>
  <pre class="lang-twig prettyprint-override"><code>{% set lastDayOfMonthEntries = craft.entries()
      .andWhere('LAST_DAY([[postDate]]) = DATE([[postDate]])')
      .all()
  %}
  </code></pre>
  <p>Add further query parameters to limit this query to a particular section or timeframe as you require.</p>
  <p>If the query is not working and you're not sure why, try sticking this code in your template to debug the query:</p>
  <pre class="lang-twig prettyprint-override"><code>{% set query = craft.entries().section('page')
      .select(['postDate', 'LAST_DAY([[postDate]])', 'DATE([[postDate]])'])
      .andWhere('LAST_DAY([[postDate]]) = DATE([[postDate]])')
      .asArray()
  %}
  
  {# Use either one to display the debug the results or dump the query to check for errors. #}
  {% dd query.all() %}
  {% dd query.getRawSql() %}
  </code></pre>
  <p>Note that queries like this are slow by nature, because the function has to be run on each entry row to check the condition, so it can't be optimized well. Though this will only have a noticable impact if you have many thousands of entries, with one entry per day for two years you'll be fine.</p>
  <p>Keep in mind that this will only work in MySQL, not in PostgreSQL. For cross-database support, you would need to replace the <code>LAST_DAY</code> function with a custom query, see <a href="https://stackoverflow.com/questions/14229038/last-day-function-in-postgres">this SE question</a> for details.</p>
  <h2>Alternative approach: List of dates in PHP</h2>
  <p>To address the comments, another way to solve this would be to generate a list of applicable dates in PHP and use that to compare <code>DATE(postDate)</code>. For example:</p>
  <pre class="lang-php prettyprint-override"><code>&lt;?php
  $startYear = 2020;
  $endYear = 2021;
  
  $lastDaysInRange = array_merge(...array_map(
      fn($year) =&gt; array_map(
          fn($month) =&gt; (new DateTime(&quot;{$year}-{$month}-01&quot;))-&gt;format('Y-m-t'),
          range(1, 12),
      ),
      range($startYear, $endYear),
  ));
  </code></pre>
  <p>Note the <code>Y-m-t</code> format; <code>t</code> gives you the number of days in the month, so you get the last day of the month using that format.</p>
  <p>Put the above function in a custom Twig extension and add it to Twig as a function. Then use the returned list of days for a custom where condition:</p>
  <pre class="lang-twig prettyprint-override"><code>{% set dates = get_last_days_of_months_in_range(2020, 2021) %}
  {% set datesList = dates|map(d =&gt; '&quot;' ~ d ~ '&quot;')|join(', ') %}
  {% set datesCondition = 'DATE([[postDate]]) IN (%s)'|format(datesList) %}
  
  {% set lastDayOfMonthEntries = craft.entries()
      .andWhere(datesCondition)
      .all()
  %}
  </code></pre>
  <p>This approach is a bit more flexible as you can easily swap out which dates you want. For the first date of the month, simply change the format from <code>Y-m-t</code> to <code>Y-m-01</code>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-22T14:00:27.370Z
Answers.Text: Great stuff! This works like a charm. It may be necessary to change this to the first day of each month.
  
  
  `FIRST_DAY` doesn't seems to be valid.
  
  Is there a solution for this?
Answers.UserDisplayName: svanzon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-22T14:19:42.533Z
Answers.Text: @svanzon Good question. The `LAST_DAY` function is useful because it takes care of leap years and stuff like that, first day is more straightforward, that's probably why there's no built-in function for that. You could use LAST_DAY, add one day and subtract one month in the query,  that will give you the first day of the month. Or use DATE_FORMAT with a hard-coded day (01). Another option would be to build an array of dates for the daterange you want in Twig/PHP, and then check if `DATE(postDate)` is in that array (see https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#hash-format)
Answers.UserDisplayName: MoritzLost
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-22T15:06:00.543Z
Answers.Text: @svanzon I just added some sample code for the second approach I mentioned. Maybe this one is a bit more flexible, as it allows you to swap out the dates you want to look for easily, and it will work cross-database.
Answers.UserDisplayName: MoritzLost
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-22T13:44:11.617Z
Answers.LastActivityDate: 2021-11-22T15:03:41.677Z
Answers.LastEditDate: 2021-11-22T15:03:41.677Z
Answers.UserDisplayName: MoritzLost
Body: <p>I'm trying to make a query for all entries on the latest of the month. So for example there is a list of entries that range from <code>01 January, 2020</code> to <code>31 October, 2021</code> each day has a separate entry. I want to show only the entries:</p>
  <pre><code>[0] 31 January 2020
  [1] 28 February 2020
  [2] 31 March 2020
  [3] 30 April 2020
  ...
  [20] 30 September 2021
  [21] 31 October 2021
  
  </code></pre>
  <p>Can someone help me out with this?</p>
  
Tags: <datetime><entry-query>
Title: Query Entries only with postDate last of month
ThreadText: # Query Entries only with postDate last of month
  
  (Asked by: username_0 on 2021-11-22)
  
  I'm trying to make a query for all entries on the latest of the month. So for example there is a list of entries that range from `01 January, 2020` to `31 October, 2021` each day has a separate entry. I want to show only the entries:
  
  ```
  [0] 31 January 2020
  [1] 28 February 2020
  [2] 31 March 2020
  [3] 30 April 2020
  ...
  [20] 30 September 2021
  [21] 31 October 2021
  
  ```
  
  Can someone help me out with this?
  
  **Tags:** datetime, entry-query
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-11-22. Score: 2
  
  You can use the [LAST\_DAY](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_last-day) function if you're working with MySQL. To only get entries with a `postDate` on the last day of the month, you can check if `LAST_DAY(postDate)` is identical with `DATE(postDate)`.
  
  ```twig
  {% set lastDayOfMonthEntries = craft.entries()
      .andWhere('LAST_DAY([[postDate]]) = DATE([[postDate]])')
      .all()
  %}
  
  ```
  
  Add further query parameters to limit this query to a particular section or timeframe as you require.
  
  If the query is not working and you're not sure why, try sticking this code in your template to debug the query:
  
  ```twig
  {% set query = craft.entries().section('page')
      .select(['postDate', 'LAST_DAY([[postDate]])', 'DATE([[postDate]])'])
      .andWhere('LAST_DAY([[postDate]]) = DATE([[postDate]])')
      .asArray()
  %}
  
  {# Use either one to display the debug the results or dump the query to check for errors. #}
  {% dd query.all() %}
  {% dd query.getRawSql() %}
  
  ```
  
  Note that queries like this are slow by nature, because the function has to be run on each entry row to check the condition, so it can't be optimized well. Though this will only have a noticable impact if you have many thousands of entries, with one entry per day for two years you'll be fine.
  
  Keep in mind that this will only work in MySQL, not in PostgreSQL. For cross-database support, you would need to replace the `LAST_DAY` function with a custom query, see [this SE question](https://stackoverflow.com/questions/14229038/last-day-function-in-postgres) for details.
  
  ## Alternative approach: List of dates in PHP
  
  To address the comments, another way to solve this would be to generate a list of applicable dates in PHP and use that to compare `DATE(postDate)`. For example:
  
  ```php
  <?php
  $startYear = 2020;
  $endYear = 2021;
  
  $lastDaysInRange = array_merge(...array_map(
      fn($year) => array_map(
          fn($month) => (new DateTime("{$year}-{$month}-01"))->format('Y-m-t'),
          range(1, 12),
      ),
      range($startYear, $endYear),
  ));
  
  ```
  
  Note the `Y-m-t` format; `t` gives you the number of days in the month, so you get the last day of the month using that format.
  
  Put the above function in a custom Twig extension and add it to Twig as a function. Then use the returned list of days for a custom where condition:
  
  ```twig
  {% set dates = get_last_days_of_months_in_range(2020, 2021) %}
  {% set datesList = dates|map(d => '"' ~ d ~ '"')|join(', ') %}
  {% set datesCondition = 'DATE([[postDate]]) IN (%s)'|format(datesList) %}
  
  {% set lastDayOfMonthEntries = craft.entries()
      .andWhere(datesCondition)
      .all()
  %}
  
  ```
  
  This approach is a bit more flexible as you can easily swap out which dates you want. For the first date of the month, simply change the format from `Y-m-t` to `Y-m-01`.
  
  > **Comments:**
  > **username_0**: Great stuff! This works like a charm. It may be necessary to change this to the first day of each month.   `FIRST_DAY` doesn't seems to be valid.  Is there a solution for this?  
  > **username_1**: @username_0 Good question. The `LAST_DAY` function is useful because it takes care of leap years and stuff like that, first day is more straightforward, that's probably why there's no built-in function for that. You could use LAST_DAY, add one day and subtract one month in the query,  that will give you the first day of the month. Or use DATE_FORMAT with a hard-coded day (01). Another option would be to build an array of dates for the daterange you want in Twig/PHP, and then check if `DATE(postDate)` is in that array (see https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#hash-format)  
  > **username_1**: @username_0 I just added some sample code for the second approach I mentioned. Maybe this one is a bit more flexible, as it allows you to swap out the dates you want to look for easily, and it will work cross-database.  
  
  ---
  
  