Convert Varchar to smalldatetime

[Riz]

I have a table with varchar column having date as 12/02/2004 I need to
convert it to smalldatetime. I'm using query select
convert(smalldatetime,col1) I get the following error : Syntax error
converting character string to smalldatetime data type.

[oj]

I have no problem convert '12/02/2004' to a small datetime. Perhaps, there
is something else in the column that violates conversion.

do to get a quick list of invalid values.

select <col>
from <tb>
where isdate(<col>)!=1

Post by Riz
I have a table with varchar column having date as 12/02/2004 I need to
convert it to smalldatetime. I'm using query select
convert(smalldatetime,col1) I get the following error : Syntax error
converting character string to smalldatetime data type.

[Hugo Kornelis]

Post by Riz
I have a table with varchar column having date as 12/02/2004 I need to
convert it to smalldatetime. I'm using query select
convert(smalldatetime,col1) I get the following error : Syntax error
converting character string to smalldatetime data type.

Hi Riz,

The date format 12/02/2004 is ambiguous. It means either February 12th or
December 2nd. I think SQL Server assumes another format than the one you
intended and another value (where the day number, interpreted as month
number by SQL Server, is above 12) causes the error.

Use CONVERT(smalldatetime, YourColumn, 101) to force format mm/dd/yyyy, or
use CONVERT(smalldatetime, YourColumn, 103) to force dd/mm/yyyy.

To prevent this in the future:
* Always store dates as a datetime or smalldatetime column.
* When you have to convert character data to datetime, always use one of
the unambiguous formats:
- For date only: yyyymmdd
- For date plus time: yyyy-mm-ddThh:mm:ss.mmm (where the optional .mmm
part denotes the milliseconds)


Best, Hugo