# Month in a query without month text

**URL:** <https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100>\
**Category:** Ignition\
**Created:** [November 15, 2021, 9:48pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100 "2021-11-15T21:48:53Z")\
**Posts on this page:** 1\
**Showing post:** 43

<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:** [November 17, 2021, 5:56pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/43 "2021-11-17T17:56:43Z")

</div>

I may be about to toss some mud in the water.

You can create a temporary table using a custom function. This will run from the Database Query Browser.

```sql
create function dbo.YearMonths(@StartDate DateTime, @EndDate DateTime)
returns @YearMonths table
([YearInt] int,
[MonthInt] int,
[MonthFullName] varchar(20),
[MonthShortName] varchar(10))
as
begin

    set @EndDate = DATEADD(month, 1, @EndDate)
    while (@StartDate < @EndDate)
    begin

    insert into @YearMonths
    select YEAR(@StartDate), MONTH(@StartDate), FORMAT(@StartDate,'MMMM yyyy'), FORMAT(@StartDate,'MMM yyyy')

    set @StartDate = DATEADD(month, 1, @StartDate)

    end

return
end

```

Sample output

```sql
SELECT * 
FROM dbo.YearMonths(DATEADD(MONTH, -6, GETDATE()), DATEADD(MONTH, 6, GETDATE()))

```

returns  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/e/eedc93f91f24f512b9675d3bfa6fc392baaa494d.png)

One of the tables I have is BOM information for production orders. Using this function with that table:

```sql
SELECT MonthFullName, qty
FROM (
	  SELECT COUNT(b.prod_order) as qty, y.MonthInt, y.YearInt, y.MonthFullName FROM bom b
	  FULL JOIN dbo.YearMonths(DATEADD(MONTH, -6, GETDATE()), DATEADD(MONTH, 6, GETDATE())) y on MONTH(b.t_stamp) = y.MonthInt
	  GROUP BY y.MonthInt, y.YearInt, y.MonthFullName, y.MonthFullName
	 ) t
ORDER BY YearInt, monthInt

```

returns  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1cfc4c3835a3dc59d07d789c82d12db59d925d18.png)

Note that this will also take care of any rollover between years.

If you need to modify the function, you can use

```sql
drop function dbo.YearMonths

```

modify the text, then execute it again to create it.

---

_[View the full topic](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100)._