Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: Time/date In A Scheduled Stored Procedure

    Hi there,

    SQL newbie here, and thanks for any help you may able to provide.

    My intention is to schedule/execute a stored procedure every morning at 12:00 a.m. that deletes all records with a column value of the day before. I.E., one of my Table columns is named POSTDAY, and could have values such as Sunday, Monday, Tuesday, etc, and on Tuesday morning, I'd like to DELETE all records with a POSTDAY value of Monday.

    I think I can do this by creating and scheduling 7 different stored procedures (each with the actual DayName), but was wondering if it's possible to just have 1 accomplish the same thing, and without having to pass any parameters to it.

    Thanks again.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    delete
    from YourTable
    where datediff(day, YourDateValue, now()) = 1
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    I must be missing something with your suggestion blindman, b/c it's giving me a "NOW() is not a recognized function" error.

    However, I did get this to work:

    DELETE FROM MYTABLE WHERE POSTDAY = DATENAME(weekday, GETDATE())

    Thanks again.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. Happens when you are working in too many database environments at once. NOW() is MS Access code. GETDATE() is SQL Server...
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And I'm kind of suprised POSTDAY = DATENAME(weekday, GETDATE()) worked for you. Is POSTDAY a text field, or is it datetime? And anyway, your syntax is going to returne TODAY's datename, and I thought you wanted to delete YESTERDAY's data...

    ...so I still think:
    where datediff(day, YourDateValue, getdate()) = 1
    ...is the way to go.
    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
  •