Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: delete 30 days from todays date?

    I have a column that is to be updated to todays date - 30 days if it is older than that.

    I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?


    update DATAFILE
    set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
    WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

  2. #2
    Join Date
    Oct 2003
    Posts
    7
    update DATAFILE
    set [Effective Date] = [Effective Date] - 30
    WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

  3. #3
    Join Date
    Jul 2003
    Posts
    123
    Ok, it was so simple?

    I found this code worked also.


    update DATAFILE
    set [Effective Date] = DATEADD(dd, -30, GetDate())
    WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

    The date is supposed to be updated to todays date minus 30 days.
    Its ok now. Thanks...

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.

    Use the DATEADD and DATEDIFF functions instead.

    blindman

Posting Permissions

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