Simple Select using variable 1

[SQLWilts]

Hiya,
I am a complete newbie at this, but I am a programmer. I simply cannot understand what I am doing wrong and I am hoping that one of you can sort me out very quickly.
I have a date on a table called History in a 10 character varchar field called date (I know, I know, poor design - not my doing I hasten to add!). I need to delete all rows from that table over 7 days old. I am trying to use this code:

/Code
DECLARE @MyDate DateTime

Delete History

Where

(SELECT (CONVERT (char(10), (substring date],1,10)), 101)) As @MyDate
Where @MyDate < (GetDate() -7)

/Code

I am getting all sorts of syntax errors here - any help gratefully received!

 

[jby1]

It was not clear to me from what you have done what your date field is called, so I have named it dtfld in the following example:

delete history
where convert(datetime, dtfld) < dateadd(d, -7, getdate())

Note that this deletes all records where the field dtfld is a date seven days or more in the past, from the exact time that the query is run. If you don't want time to be considered, then that is an exercise for you to work out ;-)

 

[SQLWilts]

Thanks jby1, if only it was that easy!
The field on the table is called "date" (very unhelpful). If I try and do as you suggested (and I have tried before) I get "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". This is because (I think) the "date" field is a 50 character varchar field. I have tried to convert it using
\Code
"CONVERT (char(10), (substring([date],1,10)), 101
\Code
and this seems to work on it's own, but how do I use it in the delete statement? This is where I am stuck.
btw, how did you get your code into that box? I thought that if I put a \code in, it would do it for me!
Thanks muchly in advance

 

[stephlal]

I'm unsure if your Mydate field is datetime or varchar or char so here's your first option...

If your field is datetime, then the below code will wipe any record that is greather than or equal to 7

DELETE
FROM History
WHERE CONVERT(int,DATEDIFF(day, MyDate, getdate()))>=7

IF your Mydate field is not a datetime format, then I need to know what format it's in. From your last post you don't seem sure. So can you post a couple of values that are currently stored in that column if the first code doesn't work for you.

 

[SQLWilts]

The problem I have is that the MyDate field is named "date", and it is a 10 character varchar. Every attempt I make at running a comparison over it gives a message saying
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.", even though I am substringing it to the first 10 chars - and the data is held in the first 10.
BTW, how did you get your code into that box? I have tried a few ways, done a search on this site to find out how to do it and can't find it.

 

[JamesLean]

Tony, what format are the dates in your table? dd/mm/yyyy? mm/dd/yyyy? some other format?

BTW, to display code in posts surround it in [ignore]

...

[/ignore]

--James

 

[SQLWilts]

Thanks for the tip James.

The date is in format dd/mm/yyyy

 

[stephlal]

I understand the data position, but like James said, we need to see values to help you further...

 

[SQLSister]

Your problem is that you have values which are not dates, things like ASAP or 2/31/2004 or 3/31/20003. YOu must first fix all of these before you can convert the values to date values. Then you need to rewrite your user interface to prevent a non date from ever being stored again. The following should tell you which records are your problem.

SELECT * from Table where isdate([DAte]) = 0

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[JamesLean]

Because the dates are in UK format rather than the default US format, you need to let SQL Server know that using SET DATEFORMAT. Try this:

SET DATEFORMAT dmy

SELECT * FROM history
WHERE CAST(date AS datetime) < GETDATE() - 7

--James

 

[SQLWilts]

That did it James, thanks loads and have a star. I guess I have to do LOADS more reading. CAST is def a useful command