Convert "nvarchar" column data type to "DateTime'

6
votes

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.

3
sqlsql-server
You have bad values. – shawnt00

3 Answers

4
votes

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.

0
votes

It depends on your setting for dateformat:

create table t(dt varchar(10));

insert t(dt) select '20/7/2020';

set dateformat mdy;

alter table t alter column dt datetime ;
--error

set dateformat dmy;

alter table t alter column dt datetime;
-- ok, because the format of the dates in the table is dmy

In your case, your values are in the format month/day/year. So you need to execute set dateformat mdy

0
votes

You have to use Month/Day/Year format. As below example you can see:

IF OBJECT_ID('TABLE1') IS NOT NULL
    DROP TABLE TABLE1
GO
CREATE TABLE TABLE1 (myDate NVARCHAR(128))
GO
INSERT TABLE1 VALUES('01-06-2020 12:00:00 AM'), ('6-17-2020 12:00:00 AM')
GO
SET DATEFORMAT MDY;
GO
ALTER TABLE TABLE1 ALTER COLUMN myDate DATETIME
GO
SELECT * FROM TABLE1

In this dataset, it will be work fine.