Convert Function?

Question

Convert Function?

Molly Weierbach

SSCommitted

Points: 1530
More actions
December 31, 2007 at 11:49 am

#181713

I have a field in one of my tables that is a varchar filed and it holds decimal values ranging from 2.7 to 2.70000002 I need to do an average on this field but I can't use aggregate operations on varchar. Can I convert this to Int to be able to do this calculation. Realistically the values that are 2.7000002 I would like truncated to 2.7 and then do an average....is this possible??

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Strommy SSCrazy Points: 2386 More actions · Answer 1

Hey Molly,

I would use the convert function as you suggested, but you would not want to use INT. You will want to use the decimal data type. Depending on what size you want, you can choose to round the values by reducing the number of decimals in the definition. Here is an example:

[font="System"]select convert (decimal(18, 8), '2.70002');[/font]

This will preserve all of the digits in this case. If you only wanted 1 digit after the decimal place, you could do this:

[font="System"]select convert (decimal(18, 1), '2.70002');[/font]

You should get the idea. Also, you may still use the average function. For more info on the nuances of decimal, check out books online.

Thanks,

Eric

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

The correct answer should be...

... change the column in the table to the correct data type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 3

I can't change the data type in the table because it's used as a free text field, values are based on INR levels for medical records. For reporting purposes I just need to change this field in a view in order to report on it, I am using Crystal 8.5 and it won't see it as a decimal number to average. So in essence I'm trying to find a work around. the convert decimal works but I have various values that need to be converted?

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

Molly Cary (1/2/2008)
I can't change the data type in the table because it's used as a free text field, values are based on INR levels for medical records. For reporting purposes I just need to change this field in a view in order to report on it, I am using Crystal 8.5 and it won't see it as a decimal number to average. So in essence I'm trying to find a work around. the convert decimal works but I have various values that need to be converted?

1 As said, always use proper datatype

2 If you cant change the column, if possible, add new column with proper datatype;update it with values from varchar column and use that column for further maipulations

3 If you want to do calculations at Crystal reports, make use of formula field to convert that to decimal and use that field in the report

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

What other values would you like to convert?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 6

It's just that one column that I need to convert all values to a decimal. I'm limited due to the way our system is setup I can't manipulate this table in anyway, therefore having to do it in the background. I've attempted to do a formula in crystal, either I'm doing it wrong or it won't work because of the data type.

Key DBA SSCertifiable Points: 6029 More actions · Answer 7

Molly,

Have you considered creating a View with the modified column structure, or a series of calculated columns off of the original "free form" Column?

If so, you could create your view, preserving the original table and data, and the build your Crystal report off of the view.

Keep Us Posted.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 8

Well my report is based off a view already, so in essense yes I guess I could do that and redefine the column there..... I've thought about that but I'm not quite sure how to do this 😀

Key DBA SSCertifiable Points: 6029 More actions · Answer 9

Molly,

The best way to learn is "under fire"! 😎

Can you drop/recreate the view? If so then ...

1) Post create table statement of the base table in question.

2) Post the Create view statement as is.

3) Let us know what column(s) need to be changed within the view.

The goal here is to modify the view in such a way that all we need to do in Crystal is change the font sizes and the like, letting SQL Server do all the behind the scenes work. I refer to this as "new paint and screen doors", or make the data look pretty.

For future reference, please see:

Forum Etiquette: How to post data/code on a forum to get the best help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

This will show you how to build a test case for others to review and work with in future postings.

Keep Us Posted.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 10

I ran this query and found that I have null values also in this column, this is messing up my calculations as well.

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 11

Molly Weierbach

SSCommitted

Points: 1530

January 2, 2008 at 7:44 am

#764042

THANKS!! I will do that Much appriciated!

Key DBA SSCertifiable Points: 6029 More actions · Answer 12

Molly,

For your NULL issue, look up the ISNULL Function in SQL Server Books On Line (BOL). It will allow you to change the value in the given column from a NULL to a default value of your specification. (I assume that a Zero will suffice here, for your calculation purposes.)

There a more ways to deal with NULL values, if that does not meet your needs.

Keep Us Posted.

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

For future reference, please see:
Forum Etiquette: How to post data/code on a forum to get the best help
http://www.sqlservercentral.com/articles/Best+Practices/61537/
This will show you how to build a test case for others to review and work with in future postings.

Man, am I glad someone besides me keeps recommending that article 😛 Thanks, Damon... if we could get all the regulars to do the same thing, we'd be able to concentrate more on helping folks fix their problems.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 14

Yes thanks....I wasn't aware of this travesty that I was doing. 😉 I have reposted (hopefully correctly).... 😀