Delete Records Before Date X 2

[TamedTech]

Hello Chaps,

This is only a simple one, and to be honest I feel like a moron for even having to ask, but its a frosty cold morning in the south of england, and my brain appears to be working at half speed.

I have a table called MessageLog which has a datetime column named LogDateTime, and I'm trying to biuld a query that will delete all records from the table before the 1st of October 2007.

I've tried the following query:

DELETE FROM MessageLog
WHERE LogDateTime < '01/10/2007 00:00:00'

And it runs without error, telling me somthing like 8,000 rows have been affected, but when looking at the data I still have a stack of records for before that date.

Am I missing somthing?

Thanks guys, sorry for such a dull question, I need to get myself a coffee.

Rob

 

[PDreyer]

It probably deleted all records before Jan 10, 2007

 

[bborissov]

That is because you have different format for date. maybe SQL Server decides that '01/10/2007' means 10 Jan 2007. When you use such construction I suggest yu always to use so called ISO format. That way you will be sure that you get the right date:

DELETE FROM MessageLog
WHERE LogDateTime < '20071001'
-- format here is yyyymmdd

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[TamedTech]

Thanks for that guys!

I used the ISO standard and it worked like a charm. It's interesting though as I opened the table to see how it was displaying the date in the field and it was in the format that I used for my first query, perhaps its just SSMS playing silly buggers and reformating it for display.

Thanks again guys, Its a lesson learned.

Rob