CODEWITHSUNDEEP
sqlfilter
Del
Del

Reputation: 1

How to filter a SQL view to this month only

Hi I'm new to this so be gentle with me, I have created a SQL view and need to filter this in sql before loading the data in excel, I want to filter the data to this month only.

So the SQL is

SELECT DocumentNo, DocumentDate, TotalNetValue, TotalTaxValue, TotalGrossValue
FROM dbo.SOPOrderReturn

And that returns this

Table

Upvotes: 0

Views: 658

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I would strongly encourage you to use a version that can use an index:

SELECT DocumentNo, DocumentDate, TotalNetValue, TotalTaxValue, TotalGrossValue
FROM dbo.SOPOrderReturn
WHERE DocumentDate >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AND
      DocumentDate < DATEADD(DAY, 1, EOMONTH(GETDATE());

Because no function is applied to DocumentDate, the optimizer would be able to use an index.

Note that EOMONTH() is available since SQL Server 2012. There are equivalent methods in earlier versions.

Upvotes: 1

nicomp
nicomp

Reputation: 4647

You need to compare the month/year of the document to the current month/year. 

SELECT DocumentNo, DocumentDate, TotalNetValue, TotalTaxValue, TotalGrossValue
FROM dbo.SOPOrderReturn
WHERE MONTH(DocumentDate) = MONTH(getdate())
AND YEAR(DocumentDate) = YEAR(getdate())

Upvotes: 0

Raj
Raj

Reputation: 502

You can use month function to get the month number and filter.

SELECT DocumentNo, DocumentDate, TotalNetValue, TotalTaxValue, TotalGrossValue
FROM dbo.SOPOrderReturn where month(DocumentDate)=9 and Year(DocumentDate)=2018

Upvotes: 0

Related Questions