Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: How to select a month range.... I'm lost

    Hey there - I have a question and would be forever greatful for a bit of help....

    I have an application where an automated service is (or should) constantly check a database group of records for a date change and flag the ones that are over 30 and 90 days old so an email notification can be sent.

    I'm having trouble writing the SQL code to do this. Any ideas?

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: How to select a month range.... I'm lost

    Originally posted by holdesb
    Hey there - I have a question and would be forever greatful for a bit of help....

    I have an application where an automated service is (or should) constantly check a database group of records for a date change and flag the ones that are over 30 and 90 days old so an email notification can be sent.

    I'm having trouble writing the SQL code to do this. Any ideas?
    What about using DATEDIFF(datepart , startdate , enddate)?

  3. #3
    Join Date
    Oct 2003
    Posts
    16

    Re: How to select a month range.... I'm lost

    Originally posted by snail
    What about using DATEDIFF(datepart , startdate , enddate)?
    Well, I guess my problem lies in that I want it automated. I won't know what the start and end date is, unless I manually change it.

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: How to select a month range.... I'm lost

    Somewhere in your data is a date field.

    You will need two different procedures or one with two loops. One for 30 day and one for 90.

    You then do:
    IF datediff (day, <DATEFIELD>, getdate() ) > 90 Then <SendEMail>
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First off, why do you need to run this constantly, so that an e-mail is sent out the very second of the day that the age of a record tops 30 days (or 90 days)? Wouldn't this be better run as a nightly job that sends out all the e-mails each morning?

    Second, the date you compare it to is the built-in function "GETDATE()", which returns the current sytem date and time. Even you go the batch job route, you should truncate it to just the day portion:

    Update YourTable
    set YourFlag = 1
    where DATEDIFF(d, startdate, CONVERT(varchar(10), GETDATE(), 120)) = 30
    or DATEDIFF(d, startdate, CONVERT(varchar(10), GETDATE(), 120)) = 90

    blindman

  6. #6
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    what u can do is try writing a stored procedure and schedule it SQL server.
    I suppose SQL server allows u to shedule a task

    cyrus

Posting Permissions

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