When I convert data from a CSV file to sql server I get a field with trailing zeros that prevent me from converting it to a date type (from a varchar which DTS defaults it to, I cant change it). I'm trying to write a SQL statment that will find those fields then delete them. Using this statement seems to get most of the fields: select date1, patindex('%0%', date1) as pos from mailboxtable2 where date1 like '% 0%' I can identify the rows with the zeros, but how do I get rid of the zeros while perserving the rest of the data? I tried using REPLACE with this to no avail, but it gives an idea of what I'm trying to do: select replace(date1, patindex('%0%', date1), ' ') as pos from mailboxtable2 where date1 like '% 0%' Any ideas???
[ August 26, 2003: Message edited by: dinesh prasad ]