You will access the system date in your calculation and use the DateDiff function. The syntax will depend on the underlying data source, Oracle, SQL Server, etc.. Example: DATEDIFF(day, date_col ,SYSDATETIME()) < 90.
ArcSDE supports five different RDBMSes that use SQL, all of which have different syntax
for referring to dates and date functions. Even using just Microsoft SQL-Server, there
are multiple possible flavors (2005, 2008, 2008R2). Dates can also be difficult due to
variability of precision -- does a time reference to a day without hour imply midnight
or noon, and in what timezone?
What you're looking for probably involves the SYSDATETIME() function and some variant
of DATEADD or DATEDIFF --
where date_col > DATEADD(day,-7,SYSDATETIME())
where DATEDIFF(day,date_col,SYSDATETIME()) < 7
but that depends on the exact types involved. This documentation page is probably a
good starting point, but you'll likely need to experiment, since different variants of the
same expression are likely to give different performance, depending on how the index
interacts with the expression.