Using date of type char in where clause

I have a SQL Server database table with a char column named "DATE" (I know, really bad, but I didn't create the database) that has dates stored in this format as strings: YYMMDD. I need to return records between these dates, so treat them as actual dates and I've tried every combination I know but still get errors. Any help is much appreciated. I've tried all of these with no luck:

SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS 开发者_StackOverflow中文版datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) BETWEEN '100401' AND '101001'
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 112) AS datetime) >= 100401 AND CAST(CONVERT(char(8), [DATE], 112) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), [DATE], 12) AS datetime) >= 100401 AND CAST(CONVERT(char(8), [DATE], 12) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 112) AS datetime) >= 100401 AND CAST(CONVERT(char(8), "DATE", 112) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST(CONVERT(char(8), "DATE", 12) AS datetime) >= 100401 AND CAST(CONVERT(char(8), "DATE", 12) AS datetime) <= 101001
SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) >= 100401 AND CAST([DATE] AS INT) < 101001
SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) >= 100401 AND CAST("DATE" AS INT) < 101001
SELECT * FROM caddb..INCIDENT WHERE CAST([DATE] AS INT) BETWEEN 100401 AND 101001
SELECT * FROM caddb..INCIDENT WHERE CAST("DATE" AS INT) BETWEEN 100401 AND 101001

Several things here:

• You choose between CAST and CONVERT, but not both
• In your case, CONVERT is more suitable because you can specify the exact format (12). Use 12 if the string has no century like 101231 and 112 if the string has century like 20101231
• When using CONVERT, you specify the data type that you want, not the data type of the original var
• MS SQL can recognize string literal as datetime if you type the string literal like '2010-10-28' yyyy-MM-dd

So, you can do something like

convert(datetime, [DATE], 12) between '2010-10-01' and '2010-10-10'

Give this a try. You need to convert all fields and constants to the DATETIME data type.

SELECT * 
FROM caddb..INCIDENT 
WHERE CONVERT(datetime, "DATE", 112) BETWEEN CONVERT(datetime, '100401', 112) and CONVERT(datetime, '101001', 112)