CODEWITHSUNDEEP
sqlsql-server
MattSom
MattSom

Reputation: 2377

Alter nvarchar column type to datetime

ALTER TABLE table_name
ALTER COLUMN columnWithDate datetime;

columnWithDate is a type of nvarchar(255), data are in 2018.06.19. form. I've checked all distinct values and there are one row with NULL value in columnWithDate.

I get the following error runninf the alter command:

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

What am I missing here?

Upvotes: 0

Views: 175

Answers (3)

Thom A
Thom A

Reputation: 95561

You need to first change your values to a unambiguous format and then change the datatype. For the value you have that would be simply removing the ., as that produces the ISO format yyyyMMdd:

UPDATE YourTable
SET YourDateColumn = REPLACE(YourDateColumn,'.','');

ALTER TABLE YourTable ALTER COLUMN YourDateColumn datetime; --Should this not be a date?

As I comment though, date would likely be a better choice here, as you have no time portion in your value(s).

Upvotes: 5

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

I suspect the extra dot(.) after the date is the culprit. In your example 2018.06.19.

This below snippet giving me the same error

DECLARE @DATE NVARCHAR(255)= N'2018.06.19.'

SELECT CAST(@DATE AS datetime)

Msg 242, Level 16, State 3, Line 3 The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

So just remove the dot from the nvarchar field befor Alter.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269823

The value is not correct somewhere in the column. I would suggest finding it:

select columnWithDate
from table_name
where try_convert(datetime, columnWithDate) is null and
      columnWithDate is not null;

If you want to remove incorrect dates, then update first:

update table_name
    set columnWithDate = try_convert(datetime, columnWithDate);

This converts the value back to a string, but the string should be convertible on your system right back to a datetime.

Upvotes: 1

Related Questions