>>Hi all, I have a table containing a DateTime column, how can I retrieve all records on or after a date such as 01/10/2012 ? I've tried
>>

>>
>> Select * 
>>from mytable
>>where convert(varchar,MyDateTimeColumn,3)  >= '01/10/12' but I'm still getting records with a date prior to that e.g I get records back with '23/07/12'  - any ideas ?
>>

>
>
>

>-- Is not affected by SET DATEFORMAT
>where MyDateTimeColumn >= '2012-10-01'
>

This is incorrect. Please read this blog post
The ultimate guide to the datetime datatypes

The correct ISO date format does not include dashes.

--------------------------------------------------------------------------
Warnings and common misconceptions
Let me say this again: you don't want to use a combination of format and type which isn't language neutral unless you make sure that you have the correct DATEFORMAT and LANGUAGE settings in your applications.

The Numeric format can use dash (-), dot (.) or slash (/) as separator. The rules for how SQL Server parses the string doesn't change depending on the separator. A common misconception is that the ANSI SQL format (sometime a bit incorrectly referred to as the "ISO format"), 1998-02-23, is language neutral for smalldatetime and datetime. It isn't. It is a numeric format and hence it is dependent on the SET DATEFORMAT and SET LANGUAGE setting:

SET LANGUAGE us_english
SELECT CAST('2003-02-28' AS datetime)
-----------------------
2003-02-28 00:00:00.000

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)
Server: Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Please read the error message closely. It says exactly what the problem is. You specify the datetime value as a string, and because the string isn't formed according to the format you are using and the DATEFORMAT setting, SQL Server cannot convert the string to a datetime value.