converting date field from Informix to SQL nvarchar

I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!

JT

The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0

varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.

So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

Run the select against the varchar column?|||You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

/*

Tuesday, April 13, 2004 13:20:17

User: sa

Server: NYCRPSTOR01

Database: Mysis

Application: MS SQLEM - Data Tools

*/

'findet' table
- 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.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0

Did you run this?

What did you get?

$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?

Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

converting a column from varchar to int

I need to copy the data from varchar column into an integer column,
obviously only copying integer values. How can I do this? I am not even
sure how to select data from a column so that the results are only the
numeric ones. Thanks for your help.Et,
ALTER TABLE should allow you to change the datatype of the table assuming
all values are integers.
You could run something like this:
UPDATE tablename
SET col2 = CAST(col1 AS int) --would require the values to be integers not
characters
Check out the ISNUMERIC function for differentiating between integers and
characters. Also see:
http://www.aspfaq.com/show.asp?id=2390
HTH
Jerry
"et" <eagletender2001@.yahoo.com> wrote in message
news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I need to copy the data from varchar column into an integer column,
>obviously only copying integer values. How can I do this? I am not even
>sure how to select data from a column so that the results are only the
>numeric ones. Thanks for your help.
>|||Yes, the isnumeric function worked, the cast of course gave me an error
because not all values are integers. This is what I did:
update tbl set newcol=originalcol
where isnumeric(originalcol)=1
Now I have to populate the remaining fields with an integer, any idea an
easy way to do that? I'd like to do it starting with the next available
number, but it doesn't really matter what the number ends up being. This
column will eventually be an identity column.
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
> Et,
> ALTER TABLE should allow you to change the datatype of the table assuming
> all values are integers.
> You could run something like this:
> UPDATE tablename
> SET col2 = CAST(col1 AS int) --would require the values to be integers
> not characters
> Check out the ISNUMERIC function for differentiating between integers and
> characters. Also see:
> http://www.aspfaq.com/show.asp?id=2390
> HTH
> Jerry
>
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:%23nc3kkdxFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||ok...clarify please...IDENTITY is system generated so you don't populate it
with any values. "any number"?
"et" <eagletender2001@.yahoo.com> wrote in message
news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
> Yes, the isnumeric function worked, the cast of course gave me an error
> because not all values are integers. This is what I did:
> update tbl set newcol=originalcol
> where isnumeric(originalcol)=1
> Now I have to populate the remaining fields with an integer, any idea an
> easy way to do that? I'd like to do it starting with the next available
> number, but it doesn't really matter what the number ends up being. This
> column will eventually be an identity column.
> Thanks!
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eMYJevdxFHA.2232@.TK2MSFTNGP11.phx.gbl...
>|||That's correct. In other words if this database had been done correctly in
the first place, it would have been an identity column. So now I need to
create this identity column based on numbers that already exist, plus
populate those records with new numbers for those records that don't use an
integer value. Once this is done, then I can make the column an identity
column so that future new records are automatically numbered correctly.
Does that make sense?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBZ%23xThxFHA.1412@.TK2MSFTNGP09.phx.gbl...
> ok...clarify please...IDENTITY is system generated so you don't populate
> it with any values. "any number"?
> "et" <eagletender2001@.yahoo.com> wrote in message
> news:O3CsMRhxFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||You cannot alter an existing column to be an identity column. You'll have to
create a new table with the identity column and then copy the rows from the
old table into the new one using SET IDENTITY_INSERT ON.
Why are you doing this? Are you planning on using the identity column as the
primary key? Analyze your data more thoroughly - there may be a better
primary key candidate.
To clean up those values try this:
http://milambda.blogspot.com/2005/0...to-integer.html
ML|||No, my primary key is a guid. This column used to be the primary key in an
access database, and was also used for other reasons. Now it's being merged
with the company's sql database. I'm only using the identity feature so it
will automatically provide a number each time a new record is created, which
of course I can do within the program it's being used with also.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:53200E31-72FB-4C15-AF6B-CF6858E3296E@.microsoft.com...
> You cannot alter an existing column to be an identity column. You'll have
> to
> create a new table with the identity column and then copy the rows from
> the
> old table into the new one using SET IDENTITY_INSERT ON.
> Why are you doing this? Are you planning on using the identity column as
> the
> primary key? Analyze your data more thoroughly - there may be a better
> primary key candidate.
> To clean up those values try this:
> http://milambda.blogspot.com/2005/0...to-integer.html
>
> ML|||If the values still need to be unique, then I'd suggest letting the server
assign them.
ML