Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: sql delete from help

    I am trying to do a delete from statement based on dates in a table. The date column is Invoice_Date and spans from 12/28/02 thru 12/27/04 (or will). I need to delete out manually nightly anything >= 12/27/03 and then it will be put back in thru a transformation. Anyway, I have ran some tests and I can't get the query to distinguish between 12/28/02 and 12/28/03. The 02 columns are deleted out also and I don't want this. Here is the query I am running. Thanks.

    DELETE FROM SalesTemp
    WHERE (INVOICE_DATE >= '12/27/03')

    go

    DELETE FROM SalesTemp
    WHERE (INVOICE_DATE >= '01/01/04')

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What version of sql are you using and have you tried to use 2003 and not 03 for the year ?

  3. #3
    Join Date
    Dec 2003
    Posts
    9
    SQL 2000 and yes I've tried to use 2003. The date column is formatted as 12/23/03 in the table. Thanks.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What type of datatype is this being stored as in the database ?

  5. #5
    Join Date
    Dec 2003
    Posts
    9
    char because it is a temp table that is later being coverted to datetime when it is transferred to the production table.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    What is the issue with using a datetime datatype ?

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    And the reason it is including 12/28/02 when you are using 12/27/03 is because 12/28 is >= 12/27 - when using char/varchar it is comparing the ascii values.

  8. #8
    Join Date
    Dec 2003
    Posts
    9
    The data coming into the temp table is coming from a rawdata textfile, that is why the char format. After I get the data there I am converting to another table in datetime format.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Explain in detail the steps that occur before the data is loaded into the temp table - and do you mean a real temp table or a holding table so you can do validation ...

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    A simple solution might be something like:

    select ... from ...
    where isdate(INVOICE_DATE) = 1 and
    cast(INVOICE_DATE as datetime) >= '12/27/03'

  11. #11
    Join Date
    Dec 2003
    Posts
    9
    You helped me answer my own question. I need to delete the data from the production table that is in datetime format because all I want to do is take the data >12/27/03 and delete and then reinput it from my flatfile. I was trying to delete from the wrong table. Everything is working perfectly now. Thanks for your help.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    Happy to help.

Posting Permissions

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