devxlogo

Dynamic Views Using the System Date

Dynamic Views Using the System Date

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...

devx-admin

Share the Post: