Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Records Before Date X 2

Status
Not open for further replies.

TamedTech

IS-IT--Management
Joined
May 3, 2005
Messages
998
Location
GB
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:

Code:
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
 
It probably deleted all records before Jan 10, 2007
 
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:
Code:
DELETE FROM MessageLog
WHERE LogDateTime < '20071001'
-- format here is yyyymmdd


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top