stored procedure, trigger, update query.NOT SURE. 1

[jmiller79]

I need to run either a stored procedure, trigger, update query. I am not sure, maybe some can help me, I have a flat file I import into my database. I have a field called [Invoice Number]. This field contains both alpha, numeric values (Ex. 000001754HRE0), what I am trying to accomplish is, removing the first 0 from every record. The field right now has 14 alpha-numeric values in it, I need to get it down to 13 ny removing the first 0 from every record. Thank You

 

[JamesLean]

1) import the data into a table - make sure the field is at least 14 chars long

2)Then run this update to chop off the leading zero:

UPDATE t
SET invnum = RIGHT(invnum, LEN(invnum) - 1)

--James

 

[jmiller79]

Thanks, I gave you a tipmaster Star, I have one more question. In this flat file i receive. I get a date field. But it come in like this 02052005. When I import this into my SQL database, it imports as text. When I import it into Excel, it imports text, When I try to convert it, it does not covert to the right date, I also get this error when I try it in Access. ADO error.. any help would be great, Thanks

 

[jmiller79]

I get this error in SQL

"Warning: The table 'UPS' has been created but its maximum row size (16227) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
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.
Warning: The table 'UPS' has been created but its maximum row size (16227) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
The statement has been terminated."