Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Between date range question

Posted on 2008-10-01
7
Medium Priority
?
373 Views
Last Modified: 2010-03-19
I am using this as an example:

BETWEEN GETDATE() - 14 AND GETDATE()

I would like to pull data that is between 9/1/2008 and 14 days prior to today.  How do I need to change the above code to make that work?
0
Comment
Question by:binaryman101
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22617435
what about this:

WHERE yourfield >= DATEADD(MONTH, -1, CONVERT(VARCHAR(10), CONVERT(DATETIME, GETDATE(), 120), 120) ) 
  AND yourfield >= DATEADD(DAY, -14, CONVERT(VARCHAR(10), CONVERT(DATETIME, GETDATE(), 120), 120) ) 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22617443
sorry, second >= should be <

WHERE yourfield >= DATEADD(MONTH, -1, CONVERT(VARCHAR(10), CONVERT(DATETIME, GETDATE(), 120), 120) ) 
  AND yourfield < DATEADD(DAY, -14, CONVERT(VARCHAR(10), CONVERT(DATETIME, GETDATE(), 120), 120) ) 

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22617456
This is what you want.

select * from YourTable
where [Date] >= convert(datetime,convert(varchar(11),getdate()-14,101),101)
and [Date] < convert(datetime,convert(varchar(11),getdate(),101),101)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:binaryman101
ID: 22617503
9/1/2008 will never change but the 14 days from now will.  Is the above solutions still applicable?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22617520
select * from YourTable
where [Date] >= convert(datetime, '09/01/2008', 1)
and [Date] <= convert(datetime,convert(varchar(11),getdate()-14,101),101)
0
 

Author Comment

by:binaryman101
ID: 22617669
I get the following message:

Error Mesage:  Conversion failed when converting datefime from character string
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22617695
Sorry... first should have been 101 as well.
...


select * from YourTable
where [Date] >= convert(datetime, '09/01/2008', 101)
and [Date] <= convert(datetime,convert(varchar(11),getdate()-14,101),101)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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