SQL Server query help... need to convert varchar to date

Question

Hi All,

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)

My Query:

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

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

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

Pls help me.

Thanks in advance

Answer 1

Convert(field,datetime,101)

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

Answer 2

you can try

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

Answer 3

Have you tried

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

Answer 4

If your strictly storing Data data on your COLUMN1, then why is your defined your data type as 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.

Answer 5

http://msdn.microsoft.com/en-us/library/ms187928.aspx