The conversion of a nvarchar data type to a datetime

0
votes

I have execute new query as below: 

alter table tenancy_extend_old alter column createdate datetime

but it show messages error as below: 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.

Please help me. thanks

1
sql-server
You have bad data because of a bad data type choice. You can identify it using this (possibly): SELECT createdate FROM dbo.tenancy_extend_old WHERE ISDATE(createdate) = 0; – Aaron Bertrand
Do you have data in table. And if yes then is it datetime data only? – Mahesh Malpani
yes, i have data such as Dec 18 201 – Izan
Well, then fix your data errors to meet the requirements of datetime (January 1, 1753, through December 31, 9999) or if you're actually storing dates from the year 201, consider using datetime2 (January 1,1 AD through December 31, 9999 AD) or use just date (January 1, 1 AD through December 31, 9999 AD) if you have a more recent version of SQL Server (2008+) and don't need time data. It's going to depend on whether 'Dec 18 201' actually refers to the year 201 or if someone clearly mistyped 2001 or 2010 or 2011. – Bacon Bits

1 Answers

0
votes

I suggest you:

-Create a new column called something like CD of type datetime -Update the data accross using a suitable format. If your data is consistently in format Dec 18 2001 (which it probably isn't assuming the old column is varchar) then you can use this:

UPDATE tenancy_extend_old
SET CD = CONVERT(DATETIME,createdate ,100)

It's likely you'll get the same error because someone chose the incorrect data type originally, so you'll need to use Aarons suggestion to find bad data

Once you've done that, drop the old column and rename the new column