August 22, 2002 at 4:54 am
I need to do a view that pulls the system date and all dates for last 12 months. Some thing like Now()- 12months or SystemDate - 12? But I'm not sure how to get it to pull the last 12 months.
Regards
comptrtoy1
August 22, 2002 at 6:50 am
Try this:
declare @my_date smalldatetime
set @my_date = dateadd(month, -12, getdate())
select *
from mytable
where date >= @my_date
and date <= getdate()
-Bill
August 23, 2002 at 3:47 am
I looked at this and said I hope that I explained this right. If I have a field called 'accountopendate' and I want to pull everything from the last 12 months from this field, then you are saying I can use the following?
declare @my_date smalldatetime
set @my_date = dateadd(month, -12, getdate())
select *
from mytable
where date >= @my_date
and date <= getdate()
I need to get my advance book out, I didn't realize you could declare and set all in the same SQL view. Any recommendations on readings?
Regards
comptrtoy1
August 23, 2002 at 4:14 am
Well actually I wouldn't declare the variable either, just put the expression in the SELECT statement - saves code and overhead of variable declaration as it's only being used once. I tend to work on the premise that if a variable is only used once, don't declare it as it is a constant, I think...
Regards
Simon
August 23, 2002 at 4:21 am
Is it safe to say this in the view:
select *
from accounts
where accountopendate <= dateadd(month, -12, getdate())
Will this give me everything from the last 12 months?
August 23, 2002 at 4:27 am
No - reverse your comparison operator !!
So change <= to >=
Actually, I wouldn't ever SELECT * from a table - better to use a column list, which you can get easily in QA by using the Object Browser, then dragging the columns from the table...
HTH,
Simon
August 23, 2002 at 4:27 am
If this is a view then it is not able to use variables. This however will do the trick and really do not need the variable in any situation.
select *
from mytable
where date >= dateadd(month, -12, getdate())and date <= getdate()
also this only goes back to the same day and hour to the secound 12 months ago. You may need to adjust a bit to have to day one of the month 12 months ago or use convert(varchar,getdate(),101) or whatever applies to you to get the whole day 12 months ago.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply