select CONVERT(date, mydate, 103)
from mytable

DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D, 03)

DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D, 03)

SET DATEFORMAT  DMY
DECLARE @D CHAR(10) = '17/08/12'
SELECT CONVERT(date, @D)
SET @D = '17/08/2012'
SELECT CONVERT(date, @D)
GO

SET DATEFORMAT MDY
DECLARE @D CHAR(10) = '8/17/12'
SELECT CONVERT(date, @D)
SET @D = '08/17/2012'
SELECT CONVERT(date, @D)
GO