Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Reminder for users

    HI All I have an database that has Access 07 as the GUI and SQL Server 2005 as the engine. I need a pop up box to let the users know that a participant has to renew their applicaiton every 3 months, which would be based on the ReferralDate field.

    Code:
    DECLARE @ReferralDate datetime = '20100401'
    IF DATEDIFF(mm, @ReferralDate, GETDATE()) > 3
    BEGIN
      PRINT 'Time to renew'
    END
    
    ELSE
    BEGIN
    
    END
    not sure if this is correct or not

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is where date math (all kinds, not just for SQL Server) gets interesting!

    DateDiff() counts the number of "beginnings" between the two dates. When using DateDiff with the mm qualifier it will return how many months started between the two dates. For either 2010-02-01 or 2010-02-28 and 2010-03-01, there is one mm beginning. For either 2010-02-01 or 2010-02-28 and 2010-05-31 there are three mm begininngs. My guess is that these answers are probably not what you want.

    I would use something more like:
    Code:
    DECLARE @ReferralDate datetime = '20100401'
    IF DateAdd(mm, @ReferralDate, 3) < GetDate()
       BEGIN
          PRINT 'Time to renew'
       END
    ELSE
       BEGIN
          PRINT 'Nope, we''re still good without a renewal'
       END
    Note that DateAdd() does it's own funky little dance at month end. Try DateAdd(mm, '2010-11-30, 3) as well as DateAdd(mm, '2009-11-30, 3) and see how you like the result. For most users that is correct, but your milage may vary!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    thank you pat, btw how are you??

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Borrowing a line from Monty Python and the Holy Grail: I'm not dead yet!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Neither is he a newt.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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