Delete * from table where date between ..... 1

[richand1]

Hi all

I know what's in the subject is far from being the correct way to do this as I did this a few months ago and forgot to save the script!

Basically, I've got a SQL datestamp applied to all records in a table. Structyre of the date is standard SQL:

2005-06-08 07:00:00

I need to delete records from this table that are between two dates, regardless of the time. I've been messing around with and I think using that is how I did it before but I can't, for the life of me, get it right!

Please help, thanks in advance!

Rich

 

[JamesLean]

Say you want to delete all dates between 1st July and 20th July (inclusive):

DELETE table
WHERE date >= '20050701'
  AND date < '20050721'

--James

 

[richand1]

Hi James

It returns this conversion error mate:

"Arithmetic overflow error converting expression to data type smalldatetime.

 

[JamesLean]

Is your date column smalldatetime or datetime?
What values are you using in your DELETE statement?

--James

 

[richand1]

Sorry James, I left out the apostrophies around the values; tried to rush it. All sorted now.

Thank you very much, for help and quick response.

Rich

 

[amrita418]

You could also use the between clause too. (as per your original post)

DELETE sds_ils_loan
WHERE  lastupdatedate between '2002-07-01' 
                               and '2002-07-21'

Regards,
AA