Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unanswered: procedure to send an email reminder help?

    Hi guys,I hope somebody can help as I am trying to write a procedure in SQL that will be an email reminder sent to users on every 5 th or the month if they don't submit data before.

    I am quite new to sql and procedures in particular so I was hoping somebody can help.

    I need to:
    1.Check the day of the month, if not 5th do nothing

    2.Get the date of the prevoious month (get current date - 1 month,set day to 1)this is the funny bit.
    I have a field DateEntered,but users only select the month and the year on the acctual page,but when submitted it gets written as a full date and defaults to the 1st of the month.

    3.Get the list of hospitals that haven't got data for the previous month

    4.Email the hospitals.

    DECLARE @returnDay int;
    DECLARE @DateEntered datetime;

    SELECT @returnDay = DatePart(day,GetDate())
    If @returnDay = 5 (syntax error near 5)
    BEGIN
    SELECT @DateEntered = GetDate() - 30
    Print DatePart(month, @DateEntered)

    I was hoping somebody could look at this and help me.

    thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You don't have to get the fifth day of the month at all. When you set up your job to run your proc, just set it Monthly, every 5th day of the month.

    You can the get the other date by:

    SELECT @datetime = DATEPART(MM,DATEADD(MM,-1,GETDATE()) + '/01/' + DATEPART(YY,GETDATE()

    Then do what you need to do.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    skunked again, (job part)

  4. #4
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Yes, but I am getting this error "Syntax error converting datetime from character string"

    I know there is a CONVERT function to do this but suprise,suprise I can't put it properly inside the formula

Posting Permissions

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