Select statement Column heading month name

1

In the statement below. I want to make the column heading be 'June' and would like to pull it from the current date. What I'm trying below is returning "Incorrect syntax near 'getdate()'

select ISNULL(sum(case when month(due_dt) <= month(getdate()) and year(due_dt) = year(getdate()) then ord_qty end),0) as CAST(getdate() as CHAR(3))
from mrreqfil_sql
where item_no = '28185' and ord_type in ('FO','CO','MR')

2

Have you tried using the MONTHNAME function?

SELECT DATENAME(mm, GETDATE());
-- OR --
SELECT DATENAME(MONTH, GETDATE());
3

Column names cannot be dynamic - you need to define the column name to a specific value.

4

Well I suppose Dynamic SQL could do that ... we take care of that in the Template for the report - i.e. we have a "Tag" in the column heading that is replaced with some data (in this case "June")

5

I would have made that recommendation - however, the OP is not filtering the data to a specific time frame that would be just that month. It appears that the OP wants to have the column name change if the data changes - which you cannot do either way.

For a report - I would use defined column name like Month1, Month2, Month3, etc.. and in the report (e.g. SSRS) we can change the labels to the actual months based on either the date range selected or the min/max values returned or something else...

1 Like
6

Ah, good point.

Can you have a Sub Heading row WHEN the month changes in SSRS?

That's how we'd handle that type of scenario in our report writer.

7

Not a sub-heading - you would use a group and group by the period or month. You could either do that in SQL or in SSRS - both work the same way.