[2008] SQL Date Query Problem

[frosty16]

Heya guys

I have got a database that has got a field in it that stores a date in the following format "17 October 2008" however it is set as a varchar field. I want to query this field to get all the records between two specific dates. I have tried changing the field to a date field however it wipes all the data that is already inserted into it (over 1500) entries. Is there anyway that i can do this?

Thank you in advance

Frosty

[GaryMazzone]

You can do 1 of three things.

1. Create a new column in the table. Uodate each row with the data contained in the other column converting to date along the way. Drop the orginal column and rename the new column the same as the old. (This can cause issued in the application code since the column is now in a different place in the table.)

2. Create a temp table holding the primary key and the data you want to convert. Modify the column datatype to Date then perform and upadte of the modified column based on the primary key and convert the data to a date type on the update.

3. Leave the column data type as is and use a conversion for every select and cast to date datatype. Personnaly I do not like this method.

My own option I would go with option 2.

[si_the_geek]

The best way is to change it to a Date field, but that is not going to be automatic due to the format of your data.

What I would recommend is to add an extra date field (so that you don't lose the original data), and then use a series of Update statements like this one:

UPDATE tablename
SET datefield = Cast('10/' + RTrim(Left(varcharfield,2)) + '/' + Right(varcharfield,4) as DateTime)
WHERE varcharfield Like '%October%'

(the highlighted parts need to be changed when you re-run it for each month)

Note that this syntax is based on SQL Server, the second line will need to change if you are using a different database system.