Dynamic Views Using the System Date

Dynamic Views Using the System Date

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])?

Well, the getdate() function returns the current date. The dateadd function can add or subtract from a date.


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


Share the Post: