CODEWITHSUNDEEP
sqlsql-servertype-conversion
cherrycharitha
cherrycharitha

Reputation: 35

sql type conversion

I have a column datedocumented in the format YYYYMMDD and of the datatype nvarchar. I want to change the data type to datetime and update the column name exdatedocumented and alter the table using ALTER .can anyone help in this.I have tried something like this 

update dbo.table2
set [DateDocumented] = convert(datetime,CAST([DateDocumented] as datetime),130)

I ended up getting error 

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

.

Upvotes: 1

Views: 67

Answers (3)

Tibor Karaszi
Tibor Karaszi

Reputation: 389

It seems we have two things going on here. Changing the type for a column. And changing the name for a column. Let's try to keep these separate so not to confuse things. I will adress the changing of type (from varchar to datetime):

First of all: Why datetime? Why note datetime2 (with whatever fractions of seconds you want, for instance datetime2(0))? The new types for date and time has been around for 10 years now!

Anyhow, you apparently have values in your table which are not valid dates! First thing you need to do is to find those rows and handle them. Lets say that you will change to datetime2(0), if not, then just change below to datetime instead:

SELECT * 
FROM dbo.Table2
WHERE TRY_CAST(DateDocumented AS datetime2(0)) IS NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You should be able to just change the column type:

alter dbo.table2 alter column DateDocumented datetime;

Your column is in a format suitable for conversion. If you wanted to use default formats instead, just do:

update table dbo.table2
    set [DateDocumented] = convert(datetime, CAST([DateDocumented] as datetime));

This should also allow the column to be converted to a datetime.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

1st change that column name then alter table

sp_RENAME 'dbo.table2.datedocumented', 'exdatedocumented' , 'COLUMN'
go
ALTER TABLE dbo.table2
ALTER COLUMN exdatedocumented datetime;

Upvotes: 0

Related Questions