Month in a query without month text

Ignition
43

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.

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

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

returns
image

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

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

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

If you need to modify the function, you can use

drop function dbo.YearMonths

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

4 Likes