Link to home
Start Free TrialLog in
Avatar of binaryman101
binaryman101Flag for United States of America

asked on

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?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

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

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)
Avatar of binaryman101

ASKER

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

Error Mesage:  Conversion failed when converting datefime from character string
ASKER CERTIFIED SOLUTION
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial