Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Posts
    30

    Exclamation Unanswered: Delete Dates > 6 months

    Hello,

    I have created a Table Called Log in SQL Server 7. I use this table as you can guess to log all transaction performed in my program. However, I would like my program to run an SQL Statement that will flush all entries in the table Log that are 6 months old from today's date.

    I am unable to find the statement that will execute this operation.

    Anyone has an idea please.

    Thanks

    Mark

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    delete from Log where Recorded_date < dateadd(month, -6, getdate())

    Assuming you did record the datetime in your table.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean like:

    Code:
    DELETE FROM yourTable
    WHERE DATEDIFF(d,DateCole,GetDate()) > 180
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by joejcheng
    delete from Log where Recorded_date < dateadd(month, -6, getdate())

    Assuming you did record the datetime in your table.

    Be careful about months...anywhere in a month counts....

    I like days better...

    No?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by Brett Kaiser
    Be careful about months...anywhere in a month counts....

    I like days better...

    No?
    But when you adjust by months, it just advances/decreases the month value of the date, maintaining the same day value (unless you are advancing/decreasing from a month with 31 days to a month with 30 days, then the days are adjusted down by one). When you adjust by days, you can't take into account 30 days verses 31 days. Adding 30 days to the current date (5/6/2004) you get 6/5/2004. Adding 1 month to the current date gives you 6/6/2004. I've never seen SQL Server arbitrarily select a day when adjusting by months.

    When you execute these two statements:
    SELECT DATEADD(m, -6, GETDATE())
    SELECT DATEADD(d, -180, GETDATE())

    You get two very different results:
    2003-11-06 13:24:14.700
    2003-11-08 13:24:14.700
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Brett: It would be better to have something like
    record_date < dateadd (dd, -180, getdate())

    Putting the column in the function call would invalidate any index on record_date.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Datediff returns integer values, but the DateAdd function is acceptable. I'd use:

    where record_date < dateadd (month, -6, getdate())

    ...since the poster did specify months.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Oct 2003
    Posts
    30
    I agree with Brett I think. I think working with days is better and safer than months. 6 months makes about 182 days.

    I must say that I did not expect so many responses in that short laps of time. I'll try what you all said and let you know.

    Thanks

    Mark

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Both are equally "safe". It's just a matter of whether you specs call for X months or X days.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    Brett: It would be better to have something like
    record_date < dateadd (dd, -180, getdate())

    Putting the column in the function call would invalidate any index on record_date.
    I'd still go with Days....

    But the above is THE most important point...

    My Original would cause a stage 2 predicat (nonsargable).

    Very Bad

    Would cause a scan...

    What he hell wa I thinking (And there you go...not thinking...AGAIN....I guess the need to focus never arose *)


    * JBuffet

    Thanks for pointing that out
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I think there are times when you need to calculate by months, and times you need to calculate by days. I think blindman put it right, it depends on the requirements of the project. If I need to do something every 30 days, regardless of the month, then I'd adjust by days, but if I need to do something on a specific day six months from now, I'd use months. Otherwise I'd have to find out which months have 30 days, and which have 31, (or 29 for a leap year) then add them together before I use dateadd() by day.

    By just adjusting by days you'd be off 7 days on average per year, or 5 if you're adjusting by 31 (not counting leap years), unless you manually compensate.
    That which does not kill me postpones the inevitable.

  12. #12
    Join Date
    Oct 2003
    Posts
    30
    In Fact I used months... It works fine and suits my needs.

    DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6


    Thanks a lot to all of you who posted.

    Mark

  13. #13
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by MCrowley
    Brett: It would be better to have something like
    record_date < dateadd (dd, -180, getdate())

    Putting the column in the function call would invalidate any index on record_date.
    Ahhhhh...I seem to remember something about this from my old SQL days...Can you explain, or point me in the direction of where I can figure out the REASON the index would be invalidated? Or is it simply because the function call would effectively "hide" the table column being considered?

    I think the gist of this is that the index column needs to be "accessable to the query" - i.e., the optimizer can't "see" into the function to know the target column is there?

    *LOL* I THINK I understand, but probably not regurgitating it in a palatable way (now THAT's just GROSS!!!)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  14. #14
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by mrhyman
    In Fact I used months... It works fine and suits my needs.

    DELETE FROM Log WHERE DATEDIFF(mm, DateL, GETDATE()) > 6


    Thanks a lot to all of you who posted.

    Mark
    Read back in some of these posts... They make mention of doing the SQL this way invalidates the index. It would be better to do:

    DELETE FROM Log WHERE DateL < DATEADD(mm, -6, GETDATE())
    That which does not kill me postpones the inevitable.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "dateadd (dd, -180, getdate())" need only be evaluated once. It returns the same value for each row, which can then be matched against an index in the table.

    "DATEDIFF(mm, DateL, GETDATE()) > 6" must be evaluated separately for each row, because the rows can and probable do return different results.
    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
  •