<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>

CC BY-SA 4.0
2021-11-22T14:00:27.370Z
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?
svanzon
CC BY-SA 4.0
2021-11-22T14:19:42.533Z
@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)
MoritzLost
CC BY-SA 4.0
2021-11-22T15:06:00.543Z
@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.
MoritzLost
CC BY-SA 4.0
2021-11-22T13:44:11.617Z
2021-11-22T15:03:41.677Z
2021-11-22T15:03:41.677Z
MoritzLost
<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>

<datetime><entry-query>
Query Entries only with postDate last of month
# 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 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).
{% 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:
{% 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 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:
 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:
{% 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**: @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)  
> **username_1**: @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.  

---

