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]Arithmetic
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 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/
"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]Arithmetic
> 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
> 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]Arithmetic
> 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
> 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]Arithmetic
> 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
> 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...
> > 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!
>|||"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...
> > How many rows do you have?
> >
> theres 1144 rows in total
>|||select rowid, convert(datetime, column1) from table1 order by rowid
something similar to that should get you the row or rows that have bad data.
mark wrote:
> "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|||mark wrote:
> "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
select rowid, convert(datetime, column1) from table1 order by rowid
if you have bad data you'll get an error with that select and then you can
easily track down the row with the bad data. "basically dates" is not good
enough for a data type conversion. sorry if i double posted this.|||This only works if you have a column called "ROWID" I do not believe SQL
server has a rowid column inherent to a table there is a ROWGUIDCOL type but
only if you build your table with it originally.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"ch" <ch@.dontemailme.com> wrote in message
news:40B251FC.A1AC90BF@.dontemailme.com...
> mark wrote:
> > "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
> select rowid, convert(datetime, column1) from table1 order by rowid
> if you have bad data you'll get an error with that select and then you can
> easily track down the row with the bad data. "basically dates" is not
good
> enough for a data type conversion. sorry if i double posted this.
>
>|||If Query Analyzer says your data is good, but you're having problems in
Enterprise Manager, try using Query Analyzer to change the datatype using
ALTER TABLE. EM might be doing something else behind the scene.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:BXosc.56$Vp5.20@.newsfe2-win...
> "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
>
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:O$5HGtaQEHA.2100@.TK2MSFTNGP11.phx.gbl...
> Can you post a zipped CSV of the data in the column?
>
hi thanks for the help, in the end i exported the data to access (no
problems) converted the field types in access
dropped the data from the sql table - and changed the field types and
re-imported back in - without any hangups
or errors - odd but still its done now!
mark|||select rowid, convert(datetime, column1) from table1 order by rowid
it also only works if you have a table named table1 and that table contains at
least two columns, one column named rowid and another column named column1.
Andrew Madsen wrote:
> This only works if you have a column called "ROWID" I do not believe SQL
> server has a rowid column inherent to a table there is a ROWGUIDCOL type but
> only if you build your table with it originally.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B251FC.A1AC90BF@.dontemailme.com...
> > mark wrote:
> >
> > > "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
> >
> > select rowid, convert(datetime, column1) from table1 order by rowid
> > if you have bad data you'll get an error with that select and then you can
> > easily track down the row with the bad data. "basically dates" is not
> good
> > enough for a data type conversion. sorry if i double posted this.
> >
> >
> >|||h
I been in same position once, my date format in string was dd/MM/yyyy
to change the date format you must use convert(datetime,'mm/dd/yyyy') if you change the column format from nvarchar to datetime it will not work. what you should to is create another column with datetime format while your current column is in use. then run a procedure to pick date from the nvarchar date column, use convert and save it into column with datetime format.
Test your data
If everything is fine, backup your table and delete column with nvarchar format
Test your application or modules using that table