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