Convert VARCHAR to DECIMAL as rows are returned???

• K. Mike Bradley

  Old Hand

  Points: 356

  More actions

  June 6, 2005 at 10:49 am

  #67531

  I am using Crystal Reports 10

  My data is a trend which for some unkown reason the vendor made VARCHAR(50)

  I need to convert it to decimal so I can do good things in Crystal Reports.

  Should I make a temp table?

  Here is my current stored procedure:

  CREATE PROCEDURE dbo.TrendRange

  @TID  int,

  @StartDate datetime ,

  @EndDate datetime

  AS

  SELECT DATE_STAMP_ "DateTime", DATA_VALUE_ "Value"

  FROM  dbo.trenddata

  WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2

  GO

  The redords returned are a datetime and VARCHAR of the value but I need decimal so Crystal reports wont choke.

  Thanks for any help you can give.

   

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  June 6, 2005 at 10:58 am

  #563284

  SELECT DATE_STAMP_ "DateTime", CONVERT(DECIMAL(Scale, Precision),DATA_VALUE_) "Value"

  FROM  dbo.trenddata

  WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2

  Regards,
  gova

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  June 6, 2005 at 3:32 pm

  #563355

  You can also use cast to convert from one data type to another.

  HTH Mike

  SELECT DATE_STAMP_ "DateTime", Cast(DATA_VALUE_ AS Decimal(19,4)) "Value"

  FROM  dbo.trenddata

  WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2