# Calculate Last Date of a Month

**URL:** <https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976>\
**Category:** General\
**Created:** [20 January 2022 15:24 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976 "2022-01-20T15:24:55Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [20 January 2022 15:24 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/1 "2022-01-20T15:24:55Z")

</div>

Hi,

I need to generate, for a mysql query '`last day of a given month`'. currently i am picking up first day of the month from a date picker in dashboard, i also have another date picker currently to pick the last date. is there an easy way to derive the last day similar to EOMONTH() function in MSExcel?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [20 January 2022 15:35 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/2 "2022-01-20T15:35:14Z")

</div>

In a function...

```auto
const selectedDate = new Date(msg.payload); //assuming date picker date is in msg.payload
const month = selectedDate.getMonth();
const year = selectedDate.getFullYear();
//msg.payload = new Date(year, month, 0).getDate(); //returns 31 for this month
//msg.payload = new Date(year, month, 0); //returns date object for last day of month
return msg;

```

Or

```auto
const selectedDate = new Date(msg.payload); //assuming date picker date is in msg.payload
const month = selectedDate.getMonth();
const year = selectedDate.getFullYear();
msg.payload = daysInMonth(month+1, year);
return msg;

/** month 1 = Jan */
function daysInMonth(iMonth, iYear)
{
    return 32 - new Date(iYear, iMonth - 1, 32).getDate();
}

```

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 January 2022 15:35 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/3 "2022-01-20T15:35:50Z")

</div>

There is a `LAST_DAY(date)` function available in mysql. For the dashboard you could just add a pulldown with the months.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [20 January 2022 15:36 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/4 "2022-01-20T15:36:45Z")

</div>

Ah I missed the key part there...

> [@smanjunath211](#):
>
> for a mysql query

Well spotted.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [20 January 2022 15:45 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/5 "2022-01-20T15:45:40Z")

</div>

Perfect!! it worked.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [20 January 2022 15:46 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/6 "2022-01-20T15:46:38Z")

</div>

Thanks for this, i could definitely make use of this elsewhere, if required out side of mysql query.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [20 January 2022 15:48 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/7 "2022-01-20T15:48:26Z")

</div>

> [@bakman2](#):
>
> you could just add a pulldown with the months

that's another great idea, i could use drop down of months rather than date, but datepicker gives me YEAR also, there must be some way to make a month-year picker, let me explore...

There is an option in month picker for year as well...... 🙂 Just noticed.

Thanks for making my dashboard leaner....

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [21 January 2022 09:33 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/8 "2022-01-21T09:33:17Z")

</div>

Hi, is there a way of limiting the period of selection in the picker ? my database has data only from September of 2021, so the picker should not go below Sep-2021 and also upper limit to current month.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [21 January 2022 12:27 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/9 "2022-01-21T12:27:05Z")

</div>

> [@smanjunath211](#):
>
> is there a way of limiting the period of selection in the picker ?

hello .. maybe you can create a datetimepicker in **ui\_template**  
using an external library that supports those options

[Datetimepicker Examples](https://getdatepicker.com/6/examples/)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/6/662357b005fefe6e908aba4005bf272f9db7b081.png)

**Example flow:**

```auto
[{"id":"d4a06909c7c81b1b","type":"ui_template","z":"54efb553244c241f","group":"397fec83949c5c0b","name":"","order":0,"width":"6","height":"6","format":"<link rel=\"stylesheet\" href=\"https://pro.fontawesome.com/releases/v5.10.0/css/all.css\" integrity=\"sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p\" crossorigin=\"anonymous\"/>\n\n <!-- Bootstrap is not required for the picker to work-->\n <script\n src=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.min.js\"\n integrity=\"sha384-cn7l7gDp0eyniUwwAZgrzD06kc/tftFf19TOAs2zVinnD/C7E91j9yyk5//jjpt/\"\n crossorigin=\"anonymous\"\n ></script>\n\n <link\n href=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css\"\n rel=\"stylesheet\"\n integrity=\"sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We\"\n crossorigin=\"anonymous\"\n />\n <!-- end bootstrap-->\n\n\n<!-- Popperjs -->\n<script src=\"https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js\" integrity=\"sha384-eMNCOe7tC1doHpGoWe/6oMVemdAVTMs2xqW4mwXrXsW0L84Iytr2wi5v2QjrP/xp\" crossorigin=\"anonymous\"></script>\n\n<script src=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/js/tempus-dominus.js\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/css/tempus-dominus.css\" rel=\"stylesheet\" />\n\n<div\n class='input-group'\n id='datetimepicker1'\n data-td-target-input='nearest'\n data-td-target-toggle='nearest'\n >\n <input\n id='datetimepicker1Input'\n type='text'\n class='form-control'\n data-td-target='#datetimepicker1'\n />\n <span\n class='input-group-text'\n data-td-target='#datetimepicker1'\n data-td-toggle='datetimepicker'\n >\n <span class='fas fa-calendar'></span>\n </span>\n </div>\n\n\n<script>\nvar theScope = scope\n\nsetTimeout(()=> {\n\n// define tomorrow to limit maxDate option\ntheScope.tomorrow = new Date();\ntheScope.tomorrow.setHours(23,59,59);\n\ntheScope.picker = new tempusDominus.TempusDominus(document.getElementById('datetimepicker1'), {\n restrictions: {\n maxDate : theScope.tomorrow,\n },\n display: {\n buttons: {\n today: true\n }\n }\n });\n\n\n }, 1000)\n\n// event handler to send msg to Node-red\n$('#datetimepicker1').on('hide.td', () => {\n let selectedDate = theScope.picker.viewDate\n theScope.send({payload: {selectedDate}} )\n });\n\n \n\n</script>","storeOutMessages":false,"fwdInMessages":false,"resendOnRefresh":false,"templateScope":"local","className":"","x":660,"y":980,"wires":[["43feacb623f686dd"]]},{"id":"43feacb623f686dd","type":"debug","z":"54efb553244c241f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":980,"wires":[]},{"id":"397fec83949c5c0b","type":"ui_group","name":"Default","tab":"a3a053c01a1ffc93","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"a3a053c01a1ffc93","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

```

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [22 January 2022 08:00 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/10 "2022-01-22T08:00:18Z")

</div>

Thanks for this, but this is beyond my comprehension 🤔. I will go through the examples and see if i can get it to work.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [22 January 2022 08:21 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/11 "2022-01-22T08:21:09Z")

</div>

I worked a bit more on the above example with options more suited for your needs.  
by adding in the options of the datetimepicker to disable date and time and also limit it to todays date.  
(also added a button to send the selected date)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/c/0c1ec4c19aa634bded92018fed00ce4d2e082603.png)

```auto
[{"id":"d4a06909c7c81b1b","type":"ui_template","z":"54efb553244c241f","group":"397fec83949c5c0b","name":"","order":0,"width":"6","height":"6","format":"<link rel=\"stylesheet\" href=\"https://pro.fontawesome.com/releases/v5.10.0/css/all.css\"\n integrity=\"sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p\" crossorigin=\"anonymous\" />\n\n<!-- Bootstrap is not required for the picker to work-->\n<script src=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.min.js\"\n integrity=\"sha384-cn7l7gDp0eyniUwwAZgrzD06kc/tftFf19TOAs2zVinnD/C7E91j9yyk5//jjpt/\" crossorigin=\"anonymous\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css\" rel=\"stylesheet\"\n integrity=\"sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We\" crossorigin=\"anonymous\" />\n<!-- end bootstrap-->\n\n\n<!-- Popperjs -->\n<script src=\"https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js\"\n integrity=\"sha384-eMNCOe7tC1doHpGoWe/6oMVemdAVTMs2xqW4mwXrXsW0L84Iytr2wi5v2QjrP/xp\" crossorigin=\"anonymous\"></script>\n\n<script src=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/js/tempus-dominus.js\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/css/tempus-dominus.css\" rel=\"stylesheet\" />\n\n<div class='input-group' id='datetimepicker1' data-td-target-input='nearest' data-td-target-toggle='nearest'>\n <input\n id='datetimepicker1Input'\n type='text'\n class='form-control'\n data-td-target='#datetimepicker1'\n />\n <span\n class='input-group-text'\n data-td-target='#datetimepicker1'\n data-td-toggle='datetimepicker'\n >\n <span class='fas fa-calendar'></span>\n </span>\n</div>\n\n<button class=\"btn btn-primary btn-sm mt-3\" ng-click=\"send({ payload: getDate() })\">Send Selected Date</button> \n\n<script>\n var theScope = scope\n\nsetTimeout(()=> {\n\n// define tomorrow to limit maxDate option\ntheScope.tomorrow = new Date();\ntheScope.tomorrow.setHours(23,59,59);\n\ntheScope.picker = new tempusDominus.TempusDominus(document.getElementById('datetimepicker1'), {\n useCurrent: false, \n defaultDate: new Date(), \n restrictions: {\n maxDate : theScope.tomorrow,\n },\n display: {\n buttons: {\n today: true\n },\n components: {\n clock:false,\n date: false,\n }\n },\n \n });\n\n\n }, 1000)\n\n// getDate function \n theScope.getDate = function() {\n return { payload: theScope.picker.viewDate.toLocaleString() }\n }\n\n\n</script>","storeOutMessages":false,"fwdInMessages":false,"resendOnRefresh":true,"templateScope":"local","className":"","x":480,"y":840,"wires":[["43feacb623f686dd"]]},{"id":"43feacb623f686dd","type":"debug","z":"54efb553244c241f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":840,"wires":[]},{"id":"397fec83949c5c0b","type":"ui_group","name":"Default","tab":"a3a053c01a1ffc93","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"a3a053c01a1ffc93","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [5 February 2022 08:21 UTC](https://discourse.nodered.org/t/calculate-last-date-of-a-month/56976/12 "2022-02-05T08:21:40Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.