Date difference between 2 dates

To give you an idea;
For getting the date use;

SELECT DATEADD(d,-7,GETDATE())

For sending email use;

CREATE PROCEDURE dbo.foo AS
BEGIN
    SET NOCOUNT ON
    -- do some other actions
    DECLARE @body VARCHAR(1024)
    SET @body = 'foo was fired '+
        CONVERT(VARCHAR, GETDATE())
    EXEC master..xp_smtp_sendmail
        @TO = 'you@you.com',
        @from = 'someone@somewhere.com',
        @message = @body,
        @subject = 'foo was fired.',
        @server = 'smtp.yourdomain.com'
END

For sending email options check this link[^].

For date functions have a look at here[^].

so this should give you an idea how you can do it.

Good luck.

Use the DATEADD [^] function

e.g.

WHERE DATEADD(d, -7, GETDATE()) >= StartDate