Post reply

Problems automating a delete statement..

  • June 9, 2004 at 11:25 am

    #85289

    We have purchased app. table that a column (called Timestamp) that is a varchar(64) column. I have been tasked with deleting data older than 90 days but it is not a timestamp ... this is how it goes into the table..  Date with the time in brackets.

    2004-06-07(18:55:01:812)

     How can I code a delete for this ?  I haven't got any ideas... The brackets are causing the problem but I don't know how to get around it...

    select Timestamp from xxx.xxx_AUDITTABLE

     where Timestamp <

      current_timestamp - 90;

     Returns this:

    Server: Msg 241, Level 16, State 1, Line 3

    Syntax error converting datetime from character string.

     Any help would be appreciated.

  • June 9, 2004 at 11:48 am

    #509707

    It looks like TIMESTAMP is really a VARCHAR column (since it has parenthesis in it), so try:

    DELETE FROM mytable

    WHERE CAST((SUBSTRING(TIMESTAMP,1,10)) AS DATETIME) < GETDATE()-90

    The SUBSTRING will get just the date portion. Then the CAST will change it to DATETIME (adding default time of 00:00:00.000).

    -SQLBill

  • June 9, 2004 at 12:13 pm

    #509714

    Thats it, case/substring... Thank You Very Much, it is just what I was looking for. Works like a charm.

  • June 9, 2004 at 1:49 pm

    #509723

    I was probably remiss in not explicitly pointing out that since the CAST changes it to a DATETIME with a default time of 00:00:00, that will cause this:

    2004-06-09(12:00:00.000)

    2004-06-09(05:23:58.000)

    2004-06-09(23:02:15.000)

    Will ALL become:

    2004-06-09 00:00:00.000

    Is that acceptable? Or do you need to delete by the actual time?

    -SQLBill

  • June 9, 2004 at 1:52 pm

    #509724

    Thanks, that is good enough. This is simply to keep the table from growing out of control. This is just a minor audit table...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply