How to extract Year together with Month from this query

Question

I have a query to show total orders by month based on a date column. May I know how to append year to the Month column?

select DATENAME(MONTH,DATEADD(MONTH, MONTH(<column>) - 1, 0)) AS Month,
    count(*) AS Total_Request
from <table>
group by month(<column>)

Gabriele Franco · Answer 1 · 2020-07-09T10:05:28.257

3

From SQL Server 2012 you can use FORMAT function

SELECT 
     FORMAT( YourColumn, 'yyyy-MMMM')
     ,COUNT(*) AS Total_Request
FROM 
    <table>
GROUP BY 
    FORMAT( YourColumn, 'yyyy-MMMM')

You can also add a culture as third parameter. Below the syntax SQL:

FORMAT ( value, format [, culture ] )

For documentation look here

edited Jul 09 '20 at 10:05

answered Jul 09 '20 at 09:54

Gabriele Franco

879
6
10

Is there any way to sort it ascending? – JL_Coder Jul 10 '20 at 01:23
added "order by Month desc" – JL_Coder Jul 10 '20 at 01:24
Add below: `ORDER BY FORMAT( YourColumn, 'yyyy-MMMM') ASC`. You can also add alias in SELECT column and use it in ORDER BY – Gabriele Franco Jul 10 '20 at 07:40
Any idea on how to query for the last 12 months even without the data? – JL_Coder Jul 23 '20 at 01:56
Seems still not able to generate months without data.. – JL_Coder Jul 23 '20 at 04:40
Look this post. Should help you do what you ask https://stackoverflow.com/questions/49943405/sql-server-getting-last-x-month-average-including-missing-months – Gabriele Franco Jul 23 '20 at 04:58

Tim Biegeleisen · Accepted Answer · 2020-07-09T12:34:23.550

2

I would just aggregate directly by the year and month, as a single text field:

SELECT
    CONVERT(varchar(7), <column>, 120) AS Year-Month,
    COUNT(*) AS Total_Request
FROM <table>
GROUP BY
    CONVERT(varchar(7), <column>, 120);

If you really want the year and three letter abbreviation for the month, you may try:

SELECT
    YEAR(<column>) AS Year,
    LEFT(DATENAME(MONTH, <column>), 3) AS Month,
    COUNT(*) AS Total_Request
FROM <table>
GROUP BY
    YEAR(<column>),
    LEFT(DATENAME(MONTH, <column>), 3);

edited Jul 09 '20 at 12:34

answered Jul 09 '20 at 06:30

Tim Biegeleisen

502,043
27
286
360

What if I prefer it to be in readable format, Month in text form. ie. May'20 – JL_Coder Jul 09 '20 at 06:35
@JL_Coder I actually recommend the format I used above, because the dates will sort properly. – Tim Biegeleisen Jul 09 '20 at 06:36
I am planning to serialize it into JSON object for visualization purpose. – JL_Coder Jul 09 '20 at 07:20
@JL_Coder I have also given you an option for the three letter month abbreviation. – Tim Biegeleisen Jul 09 '20 at 07:28
@TimBiegeleisen . . . Do not use `varchar` without a length specifier in SQL Server. The default varies by context. To avoid hard to debug errors, it should never be recommended. – Gordon Linoff Jul 09 '20 at 12:20
@Gordon Wish I could always write code right the first time. But, if I could do that, then I'd be you `:-)` – Tim Biegeleisen Jul 09 '20 at 12:35

How to extract Year together with Month from this query

2 Answers2