question

Have you tried CONVERT?

DECLARE @test [varchar] (20)
SET @test = '2010-06-11'
SELECT CONVERT([datetime], @test)

Or CAST?

DECLARE @test [varchar] (20)
SET @test = '2010-06-11'
SELECT CAST (@test AS [datetime])

When working with dates I always convert them to a known standard. For example most of the time I opt for converting them to something like this... SELECT CONVERT(VARCHAR(10),GETDATE(),110) will get you date with no time portion - useful for looking at betweens of whole dates for example. What is the interesting part is the third argument of the convert function that is called the style argument. Check BOL for more on this - it lists all of the styles that are supported and explains what to do when converting to datetime or when you convert to character data.

Cast and convert are your friends when dealing with dates and are only the tip of the iceberg.

You probably have data in your table that is not a date. Then your conversion will not work, not matter which conversion method you chose.

Have you tried the IsDate() function?

SELECT Convert(varchar(19), dob, 121) from yourTable where isDate(dob)=1

When working with dates as input from text, I always convert to the format 'YYYYMMDD hh:mm:ss.nnn', because that's a format that will always work regardless of regional settings.

If you have dates stored as 'YYYY-MM-DD', then you should convert to this to be totally sure it's interpreted correctly: left(dob,4)+substring(dob,6,2)+substring(dob,9,2) + ' 00:00:00.000'

If the value is stored in a string-column (like char, or varchar), you don't have to add any quotes.

Please note also that if you store 'YYYYMMDD' in a char(10)-column, it's stored as 'YYYYMMDD ' (two blanks in the end). I haven't tested, but I suspect that might also lead to problems when using implicit conversion.