?
Solved

SQL Server Date Range Filter

Posted on 2011-10-07
5
Medium Priority
?
247 Views
Last Modified: 2012-05-12
Can someone give me the SQL needed to filter by a date range in a "where" clause?

I am looking for something like this:

select *
from tableName
where EffectiveDate between <<a>> and <<b>>

In this example, I want <<a>> to be the first day of the month 6 months prior to the current date (today this would be 4/1/2011) and I want <<b>> to be the last day of the month 6 months in the future (today this would be 3/31/2012).

<<a>> and <<b>> should be 1 full year apart (i.e. 4/1/2011 - 3/31/2012).
0
Comment
Question by:jbaird123
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:vincem1099
ID: 36931965
Try this code
select *
from tableName
where EffectiveDate between convert(date,convert(varchar,month(DATEADD(month,-6,GETDATE())))+'/1/'+convert(varchar,year(DATEADD(month,-6,GETDATE())))) and convert(date,convert(varchar,month(DATEADD(month,6,GETDATE())))+'/1/'+convert(varchar,year(DATEADD(month,6,GETDATE()))))

Open in new window

0
 

Author Comment

by:jbaird123
ID: 36932108
vincem1099:

I tried it and there are two problems:

1. I get a message saying that "date" is not a valid type, so I changed this to "datetime".  
2. The second date returned is actually 4/1/2012, but I need this to be 1 day prior (i.e. 3/31/2012).
0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 2000 total points
ID: 36932171
Ok, I changed first date to calculate one day prior
select *
from tableName
where EffectiveDate between select dateadd(day,-1,convert(datetime,convert(varchar,month(DATEADD(month,-6,GETDATE())))+'/1/'+convert(varchar,year(DATEADD(month,-6,GETDATE()))))) and convert(datetime,convert(varchar,month(DATEADD(month,6,GETDATE())))+'/1/'+convert(varchar,year(DATEADD(month,6,GETDATE()))))

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36932286
Safest way:

select *
from tableName
where EffectiveDate >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 6, 0) AND
    EffectiveDate < DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 6, 0)

Open in new window

0
 

Author Closing Comment

by:jbaird123
ID: 36932439
thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question