Results 1 to 2 of 2

Thread: Send email prior to a datetime

  1. #1
    Join Date
    Aug 2003
    Location
    Christchurch New Zealand
    Posts
    3

    Thumbs up 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

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •