More efficent way to select rows by date as a nvarchar

Hi all wonder if someone could advise a more efficient way to select rows from a table that has roughly 60 millions records in it. Each row has a date stored as a nvarchar, for example '20110527030000.106'. I want to select all rows that are 3 months or older based on this date field, so for example i'm only interested in the first part of the date field; '20110527'. I have the following code to do that, however its a bit slow and wondering if there was a better way?

DECLARE @tempDate varchar(12)
SET @tempDate = convert(varchar(12),DATE开发者_StackOverflowADD(m,-3,GETDATE()),112)

SELECT *
  FROM [TABLE A]
  WHERE SUBSTRING([DATE_FIELD],0,8) < @tempDate

---

Your query not only it can't use any index on [DATE_FIELD] and does a full scan but it also applies the SUBSTRING() function to all values of the (date_field column of the) table.

Don't apply any function on the column so the index of [DATE_FIELD] can be used and the function is only applied once, at the calculation of @tempDate :

SELECT *
  FROM [TABLE A]
  WHERE [DATE_FIELD] < @tempDate

The < comparison works for varchar values. The following will evaluate to True:

'20110526030000.106' < '20110527'

---

Is there any reason that the datetime is not stored as datetime type?

---

If you can modify the table you could add a datetime column and then run an update to populate it with the correct data.

If you can't modify the table then you could create a new table with a datetime column, extract the keys from the table you want to query into it and enforce a foriegn key contraint across the tables. Then you can popluate the datetime column as before and then join the tables when querying.

If you can't modify anything then I guess yiou could try benchmarking your solution against a solution where you cast the varchar date into a datetime on the fly (with a user defined function for example). This may actually run faster.

Hope this helps you some..

---

If you can modify the database you could add a new field isolder3months and set it to 1 for each new entry.

With triggers you can update that once a day for every entry with isolder3months = 1. This way you check / update only 1/n th of your entries.

This solution is only practical if 3 months is fix and if this query is used often.

Then your query would look like

SELECT *
FROM [TABLE A]
WHERE [isolder3months] = 1