Convert varchar colum 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

You can use:
SELECT ID, CAST(VarcharCol As SmallDateTime) as DateTimeCol From Test1
This will return a table with varcharcol values as smalldatetime Then update the content of varcharcol with the new values.

Answer 2

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)