CODEWITHSUNDEEP
sqlsql-servert-sqldatefilter
Zolt
Zolt

Reputation: 2811

Apply filter to future dates in sql

I have a column of dates as such 

1. 01-Mar-2012
2. 01-Apr-2012
3. 01-May-2012
4. 01-Jun-2012
5. 01-Jul-2012
6. 01-Aug-2012
7. 01-Sep-2012
8. 01-Oct-2012
9. 01-Nov-2012
10. 01-Dec-2012
11. 01-Jan-2013
12. 01-Mar-2013
13. 01-Apr-2013
14. 01-May-2013
15. 01-Jun-2013
16. 01-Jul-2013
17. 01-Aug-2013
18. 01-Sep-2013
19. 01-Oct-2013
20. 01-Nov-2013
21. 01-Dec-2013

I simply want to apply a filter to the data so that the records stop at a certain month (lets say April for now) in 2013.

I've tried the following but can't think of the last part: 

WHERE DATEPART(year, Q.TermDate) <= DATEPART(year, DATEADD(YEAR, 1, GETDATE())) 
AND DATEPART(MONTH, Q.TermDate) <= ...)

Can anyone please give a suggestion?

Upvotes: 1

Views: 1442

Answers (4)

Pittsburgh DBA
Pittsburgh DBA

Reputation: 6783

Assuming a year and month parameter:

DECLARE @siYear smallint
DECLARE @tiMonth tinyint
SET @siYear = 2013
SET @tiMonth = 4

SELECT
  MyTable.MyDateValue
FROM
  MyTable
WHERE
  MyTable.MyDateValue <= CAST(RIGHT('0000' + CAST(@siYear AS varchar(4)), 4) + '-' + RIGHT('00' + CAST(@tiMonth AS varchar(2)), 2) + '-01' AS datetime)

For readers of my opinion of <= vs < for date boundaries, change the predicate to:

WHERE
      MyTable.MyDateValue < DATEADD(day, 1, CAST(RIGHT('0000' + CAST(@siYear AS varchar(4)), 4) + '-' + RIGHT('00' + CAST(@tiMonth AS varchar(2)), 2) + '-01' AS datetime))

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

Fairly straight forward;

SELECT * FROM Q WHERE TermDate<'2013-04-01';

Demo here.

Upvotes: 1

Lamak
Lamak

Reputation: 70668

You can write your statement like this:

WHERE CONVERT(VARCHAR(6),TermDate,112) <= '201304'

But this will not use any index that you could have on your TermDate column. So you can just do:

WHERE TermDate < '20130501'

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67115

If these are already datetimes, then just use a <= 

SELECT * FROM TABLE WHERE TermDate <= '01-Apr-2013'

Upvotes: 2

Related Questions