Problems automating a delete statement..
-
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.
-
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
-
Thats it, case/substring... Thank You Very Much, it is just what I was looking for. Works like a charm.
-
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
-
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