CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Norm Men
Norm Men

Reputation: 11

T-SQL select records for the month

I have a table called logD, with a field named date (datefield type). Format is "year-month-day" IE: 2011-04-11

If today's date is 2011-07-31, I want all the records for the month of July. If today's date is 2011-02-14, I want all the records for the month of Feb and so on.

I am using SQL 2008 and reporting services to run a monthly report.

Upvotes: 1

Views: 6076

Answers (3)

p.campbell
p.campbell

Reputation: 100557

Try this:

DECLARE @firstOfMonth smalldatetime = dateadd(month,datediff(month,0,getdate()),0);

SELECT * FROM logD
WHERE [date] > @firstOfMonth 
AND   [date] < dateadd(month,1,@firstOfMonth);

Upvotes: 1

Lamak
Lamak

Reputation: 70638

If you have an Index on the column DATE then you can try this one:

SELECT *
FROM logD
WHERE [DATE] BETWEEN CONVERT(VARCHAR(6),GETDATE(),112)+'01' AND DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(VARCHAR(6),GETDATE(),112)+'01'))

But, man, it looks ugly...

Upvotes: 3

Chandu
Chandu

Reputation: 82893

Try this:

SELECT *
  FROM logD
 WHERE YEAR(DATE) = YEAR(GetDate())
   AND MONTH(DATE)  = MONTH(GetDate())

Upvotes: 4

Related Questions