# Month in a query without month text **URL:** \ **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:** 20\ **Page:** 1
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 15, 2021, 9:48pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/1 "2021-11-15T21:48:53Z")
how do you get a column of your table to say the name of the month and the year for which some events happened? I want to display data in rows by the last six months so for the first column June 2021, July 2021...November 2021 SQL function Month returns the number for the month given a date. I don't see a function for naming the month. Then the second part is, does each row need to be a separate query? because I want to get totals, but each of them per month. I am using transaction group, and it is early so I can change my group too. I have a tstamp, I have a column for the number of the month of an event, but I need it for the ranges ahhh edit I think I need case statement still confused about how I would get the data for the ranges into different rows though Another solution: > [@Month in a query without month text](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/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), … ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [November 15, 2021, 10:01pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/2 "2021-11-15T22:01:57Z")
Can you post your code? Are you familiar with the AS keyword? > **[SQL AS](https://www.w3schools.com/sql/sql_ref_as.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. I think you may need to do your Sum or Count or whatever you’re doing, then group by month, and use AS to change the column name ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [November 15, 2021, 10:26pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/3 "2021-11-15T22:26:21Z")
I don’t use transaction groups, but you can get get the date formatted the way you want like this MSSQL: ```auto format(dateadd(S,t_stamp/1000, '1970-01-01'), 'MMMM yyyy') as tstamp ``` It’s easier if you use MYSQL: ```auto from_unixtime(t_stamp/1000, '%M %Y') as tstamp ``` I didnt test the MSSQL code, the conversion for unixtime specifically. While not an answer to your question, it may get you started. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 15, 2021, 11:13pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/4 "2021-11-15T23:13:07Z")
I think you are describing a [PIVOT](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15) operation, in addition to wanting the name of the month. In MS SQL Server, you’d use [FORMAT](https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings)(someDate, ‘MMMM’) for that part. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 1:27pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/5 "2021-11-16T13:27:38Z")
I am using sql. I forgot to specify. if my format uses MMMM, then it will display text for the month? this works in sql? I will test this. Thanks thanks for the tip @Matrix_Engineering about the AS. Just a couple days ago I had learned about it for renaming columns. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 16, 2021, 1:35pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/6 "2021-11-16T13:35:10Z")
> [@zacharyw.larson](#): > > I am using sql. That tells me nothing. SQL is a generic name. Specific DB brands would be something like "MS SQL Server", "MySQL", "MariaDB", "PostgreSQL", or "Oracle". Or random others. Syntax and available functions do vary. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 16, 2021, 2:36pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/7 "2021-11-16T14:36:02Z")
> [@zacharyw.larson](#): > > Then the second part is, does each row need to be a separate query? > because I want to get totals, but each of them per month. You will use a group by statement for that ```auto select sum(someColumn) as Total, FORMAT(someDate, 'MMMM') as month where ... group by month(someDate) ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 3:26pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/8 "2021-11-16T15:26:48Z")
MS Sql I have to tendency to think of MS as regular the first column shows “2021-Oct” for example if I understand, I think I will try this ```auto select FORMAT(t_stamp, 'yyyy-MMMM') as Year-Month, sum(someColumn) as Total where (t_stamp>DATEADD(month, -6, GETDATE())) group by Year-Month(t_stamp) ``` ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 16, 2021, 3:37pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/9 "2021-11-16T15:37:49Z")
> [@zacharyw.larson](#): > > `group by Year-Month(t_stamp)` I don't think that will work, Year-Month is not a built-in MS-SQL function. Also, I don't think you can group by an aliased column. You would probably need ```auto group by FORMAT(t_stamp, 'yyyy-MMMM') ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 3:43pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/10 "2021-11-16T15:43:37Z")
LOL I can’t tell if it worked because I only have data in November lol My formatting came out as 2021-November it did not like Year-Month, used Year\_Month ```auto select FORMAT(t_stamp, 'yyyy-MMMM') as Year_Month, sum(someColumn) as Total from tableName where (t_stamp>DATEADD(month, -6, GETDATE())) group by FORMAT(t_stamp, 'yyyy-MMMM') ``` going to have to add some data to the table to test it ```auto select FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total from tableName where (t_stamp>DATEADD(month, -6, GETDATE())) group by FORMAT(t_stamp, 'yyyy-MMM') ``` MMM gave me 3 letters for the month ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 4:00pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/11 "2021-11-16T16:00:52Z")
well I have a new issue it is ordering the months alphabetically jun nov sep instead of by the sequential date However, it is counting and grouping them correctly. I tried adding desc to the end of an `order by FORMAT(t_stamp, 'yyyy-MMM') desc` sep nov jun edit oh I think I get it, I don’t use format on orderby ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 16, 2021, 4:08pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/12 "2021-11-16T16:08:27Z")
Push your query into a subquery that exposes a t\_stamp for each month, then order by that in the outer SELECT. Something like this: ```auto SELECT Year_Month, Total FROM ( select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total from tableName where (t_stamp>DATEADD(month, -6, GETDATE())) group by FORMAT(t_stamp, 'yyyy-MMM') ) subq ORDER BY earliest ``` ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 16, 2021, 4:35pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/13 "2021-11-16T16:35:51Z")
I don’t think you need a subquery to order by t\_stamp. You can just add ```auto ... order by t_stamp ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 6:57pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/14 "2021-11-16T18:57:48Z")
I got an error for when adding t\_stamp. Said it was not in my group by or my select. The subquery worked. It seems very useful that the subquery allows organizing by data that is not shown in the final display. Thanks for showing me this. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 16, 2021, 7:18pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/15 "2021-11-16T19:18:10Z")
interesting, it works in MariaDB/MySQL ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 16, 2021, 7:24pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/16 "2021-11-16T19:24:31Z")
> [@zacharyw.larson](#): > > subquery allows organizing by data that is not shown in the final display You don't need a subquery just for that. Any data exposed by a FROM clause when _ **not grouping** _ is available for use in ORDER BY, whether in the SELECT list or not. When using a subquery, only items specified in the inner SELECT list are available to the outer FROM. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 16, 2021, 9:17pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/17 "2021-11-16T21:17:31Z")
So I noticed that I only get values for the months that have data. I found an old post that used `coalesce(datathatisnull,0)` and this should help me fill in the zeros. ```auto SELECT Year_Month, coalesce(Total,0) FROM ( select min(t_stamp) AS earliest, FORMAT(t_stamp, 'yyyy-MMM') as Year_Month, sum(someColumn) as Total from tableName where (t_stamp>DATEADD(month, -6, GETDATE())) group by FORMAT(t_stamp, 'yyyy-MMM') ) subq ORDER BY earliest ``` this did not work also tried the coalesce on the sum(someColumn) did not work edit as I think about it, I don’t have values of timestatmps for those months to be null to set them to 0 I just have a lack of any data in those months at all so I need to somehow check each month, see the null, and then coalesce that value to 0 I think looks like the internet says create a table of all months, left join it ---
**Author:** ![josborn](https://avatars.discourse-cdn.com/v4/letter/j/4bbf92/32.png) [@josborn](https://forum.inductiveautomation.com/u/josborn)\ **Post date:** [November 16, 2021, 9:51pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/18 "2021-11-16T21:51:49Z")
I don’t know if its the best way, but i have tackled this by using a calendar table and outer join to your actual data. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 16, 2021, 10:11pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/19 "2021-11-16T22:11:32Z")
> [@zacharyw.larson](#): > > looks like the internet says create a table of all months, left join it Yes, in MS SQL Server, that is probably the easiest. In more capable databases, I'd use a table generating function to dynamically supply a range of months. In PostgreSQL, it would be the [generate\_series()](https://www.postgresql.org/docs/10/functions-srf.html) function. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 17, 2021, 1:22pm UTC](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100/20 "2021-11-17T13:22:55Z")
Is postgresql free? there is a way to make a table in that and import it to ignition? Or you mean that it would be nice, but have to make the table in Ignition? I am not sure how I make this month table. I think one column is the month, and has the formal name, then the other is a column of numbers 0-11. [Next page](https://forum.inductiveautomation.com/t/month-in-a-query-without-month-text/53100.md?page=2)