I am trying to run the following query:

ALTER TABLE dnb_profile
ALTER COLUMN [family update date] datetime

and I keep getting the following error:

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

Can anyone tell me how I can do this successfully??

Thanks,

Connie Sawyer
Foley & Lardner

[quoted text, click to view]

Hi Connie,

This indicates that at least one value currently in the [family update
date] column is of a format that won't convert to SQL Server properly.
There may be various explanations:

1. Someone managed to enter some gibbledygook in the column - possible,
since it's of the char data type. True rubbish would result in another
error message, but dates like february 30, december 53 or some date in
month number 17 would yield this message.

2. The contents of the column may look like normal dates to you, but not
to SQL Server. The error message you got is quite common if SQL Server
interprets day as month and month as day. Remember that there are manu
different notation styles for dates. The only unambiguous date formats are
yyyymmdd (for date only) or yyyy-mm-ddThh:mm:ss.mmm (for date and time,
where .mmm, denoting the milliseconds, is optional).

In each case, you'll have to inspect your data to find the cause and
either manually fix the offending rows (if there are just a few) or do
some string massaging to change from a misunderstood date format to one of
the standard formats before converting.

Best, Hugo
--

[quoted text, click to view]

What you should do is to run this:

SELECT * FROM dnb_profile
WHERE ISDATE([family update date])=0

This will return you all the records
where value of [family update date] can't be converted to date.
And you should fix those records before altering the column.

Here's the link to the isdate function:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_8ov9.asp

WYGL,