Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011

    Unanswered: Delete SQL Records Based on Derived Column Content

    I'm hoping someone can help me with this. I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:

    •Delete from table where Date_Imported like '2011-11-18%'
    •Delete from table where Date_Imported like '2011-11-18'
    •Delete from table where Date_Imported = 2011-11-18
    It comes back saying "0 rows affected", even though I know there are records with that date in the table. Any thoughts? I'd appreciate your assistance. Thanks!

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you are asking for a range of values

    the first acceptable value would be '2011-11-18 00:00:00'

    the last acceptable value would be '2011-11-18 23:59:59'

    note that depending on the database management system being used, there might be fractions of a second after 23:59:59

    so to avoid the mess of trying to pin down what the latest acceptable value before midnight of the following day might be, it's simpler just to use an open-ended upper limit on the range
    WHERE Date_Imported >= '2011-11-18 00:00:00'
      AND Date_Imported  < '2011-11-19 00:00:00'
    notice that it's "greater than or equal" for the lower end of the range, but "less than" for the upper

    also, it is usually written like this --
    WHERE Date_Imported >= '2011-11-18'
      AND Date_Imported  < '2011-11-19'
    make sense?

    the neat part about this approach is that it works correctly both for DATETIME as well as DATE columns

    but the best news is yet to come...

    using this range test means that the optimizer can utilize an index on the Date_Imported column

    if you use LIKE, which operates only on character strings, you are forcing a conversion of the column value from datetime ro date to string

    the term for that is non-sargable and it means very poor performance | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011


    Thank you.......that was very informative!

Tags for this Thread

Posting Permissions

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