Clear all records after given date. 2

[HestonJames]

Afternoon Guys,

I've got a table with a DateTime column and I want to form a query which will remove all records from that table who's DateTime column is greater than a given date.

So, my first instinct would be to write something like this:

DELETE FROM MyTable
WHERE DateColumn > '2008-01-25 00:00:00'

However, something tells me that this is a risk and that the datetime shouldnt be in a string format like that, correct?

I'd appreciate your thoughts on how to make it a little safer so i dont go deleting a load of records i dont want to delete.

Heston

 

[gmmastros]

There's nothing inherently wrong with it (but it could be better).

First, understand that SQL Server will automatically convert your string so that it matches the data type of the column. Then, it will run the query.

The only problem with this method involves some complicated stuff, but you should really become familiar with it. You see, each database has a default language (english, us_english, spanish, french, etc...). Some languages use month-day-year, and others use day-month-year.

Also, understand that the default language does NOT directly affect this. However, when you create a login, the login gets the database's default language. It's the login's default language that identifies the way dates are interpreted. Of course, you can change the default language for a user and/or database.

I mention this because.... years from now, you may find yourself in a situation where the default language is something other than what you expect it to be.

So.... 5/6/2008... is that May 6, or June 5th?

With SQL Server, there are 2 unambiguous date formats.

YYYYMMDD HH:MM:SS
YYYY-MM-DD[!]T[/!]HH:MM:SS

If this were my code, I would use...

DELETE FROM MyTable
WHERE DateColumn [!]>=[/!] '20080125'

You do not need to specify the time if you want midnight. Also, notice I changed this to >= so that rows with 2008-01-25 (without a time, at midnight) will also get deleted.

If you use one of the unambiguous date formats, the code will never fail, no matter what the language settings are.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

George,

Sorry - while this one has popped up I thought I would quickly pick your brain.....

Apart from the time segment, obviously, is there any reason to steer clear of delete blah blah where xyx <= (or greater / = to) select getdate () -x?

TIA,

M.

 

[HestonJames]

George,

Thank you mate, that was a perfect example and makes excelent sense, I thought i'd seen the 'safe' formats for datetimes before which is what prompted me.

Thats a nice comment about the language being inherited from the database for each user, this will no doubt come in handy one day. Whats the deal with timezones? do they work on the same premise or is that determined by the server?

Many thanks,

Heston

 

[gmmastros]

mutley1,

I see no reason to steer clear of using GetDate() within a where clause. The problem people get in to with GetDate() is when they want to take time in to consideration.

It's important (for performance) that you have sargable joins and where clauses.

For example... Suppose you want to delete rows starting older than 7 days. There are various ways to do it. Some good, and some bad.

[tt][blue]
Delete
From TableName
Where DateColumn <= GetDate() - 7
[/blue][/tt]

If you run this at 9:00 AM, the rows before 9 AM will be deleted, but the rows after 9 AM will not.

There are BAD ways to accommodate the time.

[tt][blue]
Delete
From TableName
Where Convert(VarChar(20), DateColumn, 101) <= GetDate() - 7
[/blue][/tt]

Convert with style = 101 will remove the time component, but there's some hidden things going on here. The left side is now returning a varchar, so SQL will need to ALSO convert it to DateTime. But, more importantly, this is no longer sargable. The conversion will need to occur on every row, so an index on DateColumn wouldn't be used.

Better would be this...

[tt][blue]
Delete
From TableName
Where DateColumn <= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) - 7
[/blue][/tt]

Here, we remove the time component from GetDate() and then subtract 7 days. More importantly, the query is sargable, indexes would be used, and the query will run faster.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mutley1]

Spot on. Thanks George.

 

[gmmastros]

Whats the deal with timezones? do they work on the same premise or is that determined by the server?

I am 90% certain that the timezone is determined by the server. I've never had to deal with an application that spanned time zones, so I probably won't be much use here. I'm fairly certain that each login does NOT have a timezone setting. There is a GetUTCDate() function in SQL Server.

According to Books On Line...

Returns the datetime value that represents the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the [red]time zone setting in the operating system of the computer[/red] on which the instance of Microsoft SQL Server is running.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom