problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arith
metic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the string
s look like? What format? Also,
you might want to check out: rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/sqlserver/in...ver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...">
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column> ) = 0
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column> ) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
> rolled
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> theres 1144 rows in total
>