# SQL query for specific month

**URL:** <https://forum.inductiveautomation.com/t/sql-query-for-specific-month/3443>\
**Category:** Ignition\
**Created:** [October 16, 2011, 3:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-specific-month/3443 "2011-10-16T15:15:45Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![rfbuehler](https://avatars.discourse-cdn.com/v4/letter/r/3ec8ea/32.png) [@rfbuehler](https://forum.inductiveautomation.com/u/rfbuehler)\
**Post date:** [October 16, 2011, 3:15pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-specific-month/3443/1 "2011-10-16T15:15:45Z")

</div>

Can some one help me to figure out the query to retrieve the records for a specific month.

I am using this for a monthly report.

Here is my query for the daily report.

SELECT distinct(Date\_Format(CurrentDateTime, ‘%m/%d/%y %H:%i’)) as Hourly\_Data, CurrentDateTime, hour(CurrentDateTime)as rptHour, head\_level, tail\_level, (head\_level - tail\_level) as net\_head\_level, U1\_mw\_hr\_mtr, U1\_unit\_flow AS U1\_CFS, U2\_mw\_hr\_mtr, U2\_unit\_flow AS U2\_CFS, U3\_mw\_hr\_mtr, U3\_unit\_flow AS U3\_CFS, U4\_mw\_hr\_mtr, U4\_unit\_flow AS U4\_CFS, volts, pf AS PowerFactor,  
(U1\_unit\_flow + U2\_unit\_flow + U3\_unit\_flow + U4\_unit\_flow) AS Total\_CFS  
FROM hourly\_data  
WHERE DATE(CurrentDateTime) = DATE(’{Root Container.Calendar.date}’) group by Hourly\_Data

I have a calendar on the Window where the report is and I am using it to select the date of the report.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 17, 2011, 9:42am UTC](https://forum.inductiveautomation.com/t/sql-query-for-specific-month/3443/2 "2011-10-17T09:42:35Z")

</div>

I usually add a couple of dynamic properties.

year:

```auto
dateExtract({Root Container.Calendar.date}, "year")
```

month:

```auto
dateExtract({Root Container.Calendar.date}, "month")+1
```

(The +1 is because the month is zero-indexed, ie January=0, February=1, …)

Then your where clause can be something like:

```auto
WHERE MONTH(CurrentDateTime) = '{Root Container.Calendar.month}' AND YEAR(CurrentDateTime) = '{Root Container.Calendar.year}' group by Hourly_Data
```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 17, 2011, 10:04am UTC](https://forum.inductiveautomation.com/t/sql-query-for-specific-month/3443/3 "2011-10-17T10:04:02Z")

</div>

Okay just had a DOH! moment… 😆

Really don’t need the dynamic properties… it was something I did while still learning.

```auto
WHERE MONTH(CurrentDateTime) = MONTH('{Root Container.Calendar.date}') AND YEAR(CurrentDateTime) = YEAR('{Root Container.Calendar.date}') group by...
```