Question:
I have a user who wants me to create a dynamic search view where the data retrieved is effective within the last six months using the system date.
Is there some sort of date editing on the select clause that I can use (i.e., where the effective date is between [sysdate] and [sysdate–6 months])?
Answer:
Well, the getdate() function returns the current date. The dateadd function can add or subtract from a date.
So
select * from [tablename]where[name of date field ] > dateadd(mm,-6,getdate())
will find all records that are within the past six months. If you want, you could also use the same select within a create view statement:
create view [view name] as select...
