Changing datatype from char to datetime

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
clsawyer@.foley.comOn 27 Sep 2004 09:15:29 -0700, Connie Sawyer wrote:

>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.

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Connie Sawyer wrote:

> 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
> clsawyer@.foley.com

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/d..._ia-iz_8ov9.asp

WYGL,
Andrey