SQL Server查询帮助…需要将varchar转换为date

I am writing a query to fetch results for all the values in a column which is of varchar type.. are less than '29/08/2010' (date)

我正在编写一个查询，为varchar类型的列中的所有值获取结果。少于“29/08/2010”(日期)

My Query:

我的查询:

SELECT * FROM EMPLOYEES WHERE COLUMN1 < '29/08/2010'

Here the column1 is of varchar type. I am using SQL Server 2005

这里的column1是varchar类型。我正在使用SQL Server 2005

Is there a way to make this possible..??

有办法使这成为可能吗?

Pls help me.

请帮助我。

Thanks in advance

谢谢提前

4 个解决方案

#1

Have you tried

你有试过

SELECT * FROM EMPLOYEES WHERE CONVERT(datetime, COLUMN1, 103) > CONVERT(datetime, '29/08/2010', 103)

#2

you can try

你可以试着

SELECT * FROM EMPLOYEES WHERE convert(datetime,COLUMN1,110) <  convert(datetime, '29/08/2010',110)

#3

Convert(field,datetime,101)

http://www.sqlusa.com/bestpractices/datetimeconversion/

#4

If your strictly storing Data data on your COLUMN1, then why is your defined your data type as varchar?

如果严格地在COLUMN1上存储数据数据，那么为什么将数据类型定义为varchar呢?

From the performance point of view, It will kill the performance, because Converting Varchar to DateTime or any other will make your indexes inefficient if there is any. Generally using functions like RIGHT() or CONVERT, LIKE'% %' or Like 'a%' will make the WHERE Clause a Non-Sargable, resulting an index scan operation to retrieve the data.

从性能的角度来看，它将扼杀性能，因为将Varchar转换为DateTime或其他任何形式的转换将使索引效率降低(如果有的话)。通常使用RIGHT()或CONVERT等函数，如'% '或'a%'将使WHERE子句成为非sargable，从而产生索引扫描操作来检索数据。

#1