August 3, 2006 at 9:13 am
This should be sooooo simple, but I cannot for the life of me make it work
I have a table that has a column (date_added) populated with getdate() values, dependant on when a row was added to the table. when trying to use that column as conditional statement, say all rows inserted in the last 90 days:
select * from tblusers where date_added >= (getdate()-90)
however, let's say I wanted to change that range for, say, rows added between three months and six months prior, how would I change the conditional phrase to make that work?
August 3, 2006 at 9:20 am
Something like this :
Declare @Today as datetime
--strip the time from the date
SET @Today = DATEADD(D, 0, DATEDIFF(D, 0, Getdate()))
select * from tblusers where date_added >= DATEADD(m, -6, @Today) and date_added <= DATEADD(m, -3, @Today)
Make sure you check out where you want to cut the last day of the period and adjust the <= to < if needed.
August 3, 2006 at 9:21 am
I would use:
where date_added between DateAdd(Month, -6, Getdate()) and DateAdd(Month, -3, GetDate())
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2006 at 2:02 am
Solution using BETWEEN is stuck with the fact, that both limits are included. Especially with datetime values, I prefer comparision using >=, < because it is easier to manage and more readable.
You may also need to tweak the code depending on whether the date_added includes time portion, and (if it does) how you want to treat this. Therefore I think that solution RGR'us posted will be better suited for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply