Convert varchar column to smalldatetime

Question

I have a table with 800+ records. In this table I have a column named 'Data' of varchar(10) datatype which contains dates in dd.MM.yyyy format.I want to convert it to smalldatetime.

I've tried converting it using Enterprise Management Studio Express, but I receive this error:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

How can I convert it?

Answer 1

I think you'll need to do a little string manipulation to get this to work as I think SQL is expecting 'MM.dd.yyyy'. So, update your table to flip-flop the month and day first, then the conversion should go through.

update YourTable
    set Data = SUBSTRING(Data,4,3) + LEFT(Data,3) + RIGHT(Data,4)