SqlServer SQL question

I have a query that I am working on to select some data from a table based on date.

Code:

SELECT strCompany, vendorID, Col009 AS weight, Col014 AS date
FROM SQL1
WHERE (Col014 BETWEEN '08/01/2006' AND '08/08/2006') AND (NOT (strCompany IS NULL))
ORDER BY strCompany, Col014

The problem is that Col14 is stored in the database as a char column not a datetime column. Since the data is char, for the dates provided it will return data from 08/02/2005 for example. How do I stop this extraneous data from being returned?

 

you can try casting Col1014 as date

CAST(Col1014 as DateTime)

But I can't say for certain if that will do it. My first though is .. "why is a date data column being stored as a char?"

 

Thanks for trying, but that won't work in the where statement. Gives an error about converting string to datetime.

And why the table is like that is because the guy before me was too lazy to figure out how to insert strings into a date field.

 

you should be able to change the field type from string to datetime. I highly recommend doing this.. AFTER you make a copy of the db and test it first of course.

 

yeah, I've been considering that

 

It seems that I can't change the data type of the column.

I also can't just add a new datetime column and insert/select into the new column single sqlserver can't parse the request. I keep getting an error saying there was an error converting datetime from string.

I am very much starting to hate sqlserver and am begining to think this is just one more shoddy Microsoft product.

 

I ended up figuring it out. A very creative use of substring methods and it seems to work just fine.

Thanks for the help, Kodo.