Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getting rid of trailing zeros

 
dinesh prasad
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
R Laksh
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What about using RTRIM?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic