CODEWITHSUNDEEP
sqlsql-server
Sboh
Sboh

Reputation: 87

Query returns all rows

enter image description here

I want to know during which month the most purchases were made. The query returns all rows, I want to count the purchases for each month.Can someone please help

SELECT COUNT(Inv_Num) AS 'NUM OF PURCHASES', DATENAME(MONTH, [Inv_Date]) AS MONTH  
FROM tblInv_info
WHERE DATENAME(YEAR, [Inv_Date]) BETWEEN '2012' AND '2020'
GROUP BY Inv_Num, Inv_Date
ORDER BY MONTH DESC, COUNT(Inv_Num);

Upvotes: 0

Views: 106

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

The problem you are having is most likely being caused by bad data in one or more records in the INV_PAID column. While I don't have an exact answer for you, if you are using SQL Server 2012 or later, then TRY_CONVERT might be one way to flush out the offending records:

SELECT *
FROM tblInv_info
WHERE TRY_CONVERT(datetime, INV_PAID) IS NULL;

Upvotes: 2

Related Questions