Convert "nvarchar" column data type to "DateTime'

Question

Does anyone know how to convert the column data type from "nvarchar" format to "datetime" in SQL Server?

Ex:

1. 01-06-2020 12:00:00 AM
2. 6-17-2020 12:00:00 AM

I've tried the below query:

ALTER TABLE MyTable ALTER COLUMN UpdatedDate datetime

But, got the below error

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Answer 1

Well, Now that you've seen how problematic it might be, I hope you've learned never to store DateTime values as strings.
You should strive to always use the most appropriate data type available.
Don't worry, though. It's such a common mistake Aaron Bertrand wrote a blog post about it (well, the more general problem) - called Bad habits to kick : choosing the wrong data type.

So here's how you deal with this sort of thing: (TL;DR; Live demo on Rextester)

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE MyTable (
    Id int identity(1,1),
    UpdatedDate nvarchar(30)
);


INSERT INTO MyTable(UpdatedDate) VALUES
('01-06-2020 12:00:00 AM'),
('6-17-2020 12:00:00 AM'),
('02-30-2020 12:00:00 AM'), -- Bad data
('1-06-2020 12:00:00 ZM'),  -- Bad data
('01-06-2020 13:00:00 PM'),
('13-06-2020 13:00:00 PM'),  -- Bad data
('01-06-2020 1:00:00 PM'),
('1-6-2020 1:00:00 AM'),
('Not a date at all...');  -- Bad data

Notice I've added some rows that contains data that can't be converted to a DateTime value.

Then, the First step is to get a list of values that will fail convert.

SELECT Id, UpdatedDate
FROM dbo.MyTable
WHERE TRY_CONVERT(DateTime2, UpdatedDate, 110) IS NULL;

This will return the following recordset (for this sample data):

Id  UpdatedDate
3   02-30-2020 12:00:00 AM
4   1-06-2020 12:00:00 ZM
6   13-06-2020 13:00:00 PM
9   Not a date at all...

Now that you have the list, you can decide whether you want to manually fix them, delete the records, or simply ignore the values, leaving the new UpdatedDate column NULL in these rows.

After you're done, you add a new column to the table, with a temporary name. I suggest using DateTime2 and not DateTime - it's just a better data type.

ALTER TABLE dbo.MyTable 
ADD UpdatedDate_New DateTime2 NULL;

Next, you populate this column (I'm assuming here you've decided to leave it null where the original column's data can't be converted to DateTime2):

UPDATE dbo.MyTable
SET  UpdatedDate_New = TRY_CONVERT(DateTime2, UpdatedDate, 110);

Now, remove the old column from the table:

ALTER TABLE dbo.MyTable 
DROP COLUMN UpdatedDate;

And finally, Rename the new column:

EXEC sp_Rename 'dbo.MyTable.UpdatedDate_New', 'UpdatedDate', 'COLUMN';

Validate the results:

SELECT *
FROM dbo.MyTable

Results:

Id  UpdatedDate
1   06.01.2020 00:00:00
2   17.06.2020 00:00:00
3   NULL
4   NULL
5   06.01.2020 13:00:00
6   NULL
7   06.01.2020 13:00:00
8   06.01.2020 01:00:00
9   NULL

There is one last issue I haven't addressed in this answer and that's the dependencies of the original column. You should check what depends on the original column, because sometimes you will have to make adjustments to code depending on the column you've changed.