Send email prior to a datetime

This is quite a tricky one for me to figure out.

I have a datetime field in a databse for when a tender closes. More often then not its 4pm - for what its worth. And I want to send an email informing the manager 6hrs, 30mins prior and when the tender closes. So I need to somehow check that field either once a day and store those times somewhere or quite regularly ... like every hour.

I would be interested to hear how people would approach such a concept. I havn't got a great deal of experience in triggers or SQL mail.

It is in an asp file that I am doing it.

Any ideas would be hugely appreciated.

Cheers

tom

calculate 6.5 hr less

select 6.5/24
--result .270833
select getdate()
--2003-08-11 09:49:03.867
select getdate() - 0.270833
--result 2003-08-11 03:19:03.897


Create a trigger on the table where you are creating tender closing date which is going to insert the 6.5 less time on a table called EmailTable (managers email id, tender name, tender id, close date and time.

Run a job every min to check the Email table comparing with system clock (getdate()) and send email accordingly.