[RESOLVED] Modify column from nvarchar(50) to a DateTime

[steve_rm]

Hello,

SQL 2005

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

[szlamany]

Do this in a query window

Put BEGIN TRAN at the top of the window and ROLLBACK at the bottom of the window - so you can test this out.

Now - in the window - in the BEGIN TRAN/ROLLBACK block you want to do a couple of things.

First is to do a SELECT of the primary key and the date column.

Next is to make the date be YYYY-MM-DD in the varchar() field. Do whatever LEFT/RIGHT/SUBSTRING or CHARINDEX stuff you need to accomplish this.

Then do another SELECT of the same two columns.

This will allow you to see that you are in fact fixing the column data properly.

Then you want to do a:

SELECT DATECOL FROM SOMETABLE WHERE ISDATE(DATECOL)=0

This will fully verify that you fixed the column to make them valid dates.

All of this in a ROLLBACK so that you can test it fully.

Once you have the dates fixed you should be able to set the column to DATETIME.

You might want to try to do it with:

ALTER SOMETABLE ALTER DATECOL DATETIME NOT NULL

and do that in the BEGIN TRAN/ROLLBACK - I believe you can ALTER TABLE in a transaction.

This will really test that the job will work.

And of course another SELECT in the BEGIN TRAN/ROLLBACK right after the ALTER TABLE - so you can see your date transformed in a test mode.

Once you are happy - change the ROLLBACK to COMMIT and run the query in the window and the job will actually be performed.

[steve_rm]

Hello,

I did it like this:

UPDATE OtherCall SET [Date] = CONVERT(NVARCHAR(50),CONVERT(DATETIME,[Date],103),111)
 
    ALTER TABLE OtherCall ALTER COLUMN [Date] DATETIME

[szlamany]

I looked quickly at CONVERT and having not much experience with DD/MM/YYYY formats I didn't see a easy way to accomplish it with one call.

Good find.