How DATEPART function can query vendor invoices by year, quarter, month and day

Question

Would you know what is missing from my SQL Server SSRS query, I am trying to extract vendor invoices by year, quarter, month and day, only from January through December 2019. My output should group by list the following information, YEAR/ QUARTER/ MONTH/ from created date.

After I run my query I get the following error:

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'year'

What could be the issue?

My code has been explained from the following website link, for reference I have included my code details for a better explanation: https://www.sqlservertutorial.net/sql-server-date-functions/sql-server-datepart-function/

I have tried the following code:

SELECT DISTINCT
        DATEPART("YEAR", ab.Created_Date) [year], 
        DATEPART("QUARTER", ab.Created_Date) [quarter], 
        DATEPART("MONTH", ab.Created_Date) [month],
        ab.Invoice_Type_ID,
        ab.Created_Date,
        ab.Vendor_ID,
        a.Vendor_Name,
        a.Vendor_Type
FROM 
    dbo.vendors AS a
INNER JOIN
    dbo.invoices AS ab ON a.Vendor_ID = ab.Vendor_ID
WHERE   
    ab.Created_Date IS NOT NULL
GROUP BY  
    DATEPART("YEAR", ab.Created_Date) [year], 
    DATEPART("QUARTER", ab.Created_Date) [quarter], 
    DATEPART("MONTH", ab.Created_Date) [month],
    ab.Invoice_Type_ID,
    ab.Created_Date,
    ab.Vendor_ID,
    a.Vendor_Name,
    a.Vendor_Type
ORDER BY  
    [YEAR] DESC, 
    [QUARTER], 
    [MONTH]

You rarely never need to do SELECT DISTINCT when you do GROUP BY, since GROUP BY eliminates duplicates. — jarlh

Alan Schofield Alan Schofield · Accepted Answer · 2020-01-17T15:08:40

You have included the aliases ([year], [quarter], [month]) in your group by expression, you need to remove the alias (but not the expression) for instance

GROUP BY DATEPART("YEAR", ab.Created_Date) [year],
...

would be

GROUP BY DATEPART("YEAR", ab.Created_Date),
...

How DATEPART function can query vendor invoices by year, quarter, month and day

1 Answers