Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

Between date range question

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
binaryman101
Asked:
binaryman101
  • 3
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
BrandonGalderisiCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
binaryman101Author Commented:
9/1/2008 will never change but the 14 days from now will.  Is the above solutions still applicable?
0
 
BrandonGalderisiCommented:
select * from YourTable
where [Date] >= convert(datetime, '09/01/2008', 1)
and [Date] <= convert(datetime,convert(varchar(11),getdate()-14,101),101)
0
 
binaryman101Author Commented:
I get the following message:

Error Mesage:  Conversion failed when converting datefime from character string
0
 
BrandonGalderisiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now