Modify a column from Nvarchar(50) to a DateTime

Hello,

I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.

This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)

However, I have to change this column to a DateTime without destroying the data.

Is there any easy way to write some script or use studio management to change this.

Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.

The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.

I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any suggestions would be most grateful,

Thanks,

Steve

The error is because your data format is 'confusing' to SQL Server. The ISO standard is [ yyyy/mm/dd ]

First you need to change the data in the existing column to be of a format that will allow the column datatype to change.

This 'should' work for you (untested):

UPDATE MyTable

SET DateEmployed = convert( nvarchar(50), convert( datetime, DateEmployed, 103 ), 111 )

Then change the datatype.