Inches to Feet & Inches

Transact-SQL
1

Hi everyone,

I ran into a little problem when converting inches to feet + inches
61 inches = 5"1
70 inches = 5"10

I am pulling my numbers with 2 numbers after the decimal soo I cant tell the difference between 5.1 and 5.10

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE) WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR) +'.'+ CAST(FLOOR(PD.DISCRETE_DATA_VALUE % 12)AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE / 2.54 AS DECIMAL(10,2))/12 AS VARCHAR(10)) WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds' THEN CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE) WHEN DD.UNIT_OF_MEASURE_NAME = 'Ounces' THEN CAST(PD.DISCRETE_DATA_VALUE/16 AS DECIMAL(10,2)) WHEN DD.UNIT_OF_MEASURE_NAME = 'Kilograms' THEN CAST(PD.DISCRETE_DATA_VALUE * 2.2 AS DECIMAL(10,2)) WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds & Ounces' THEN CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE) END AS FINAL,

Any ideas on how I could fix this?

2

There is no difference.

1 Like
3

I have decimals set as (10,2)

so 5.1 shows as 5.10 and 5.10 shows as 5.10

4

decimal(10,2) does not control the display format. Has nothing to do with it in fact. It just says that you want space for 8 digits before the decimal point and 2 after.

2 Likes
5

would there be a way to show 2 decimals on values over 9 and truncate it to 1 for values under 10
after the decimal..
sorry I know this sounds confusing

6

yes, it sounds confusing. however, as a general rule of thumb, this kind of formatting belongs in the presentation layer (web site, report, whatever) not in the database.

1 Like
7

Maybe this?

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS varchar(8)) +'.'+ CAST(FLOOR(PD.DISCRETE_DATA_VALUE % 12)AS varchar(2))
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS varchar(8)) +'.'+ CAST(FLOOR(PD.DISCRETE_DATA_VALUE % 12)AS varchar(2))
WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 2.54) AS varchar(8)) +'.'+ CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 2.54 % 12)AS varchar(2))
1 Like
8
WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR(8)) +'.'+ CAST(FLOOR(PD.DISCRETE_DATA_VALUE % 12)AS VARCHAR(2)) after the + '.'+

CAST(FLOOR(PD.DISCRETE_DATA_VALUE % 12)AS VARCHAR)

is where I need to be able to display 1 character or 2
but since I had it always displaying 2 ... 5'1 and 5'10 look the same and I cant tell the difference unless I look at the inches..

9

Quite true, you're right. You also need to change the column from a decimal(10,2) to varchar. Make sure all the values from that column are cast as varchar rather than as decimal.

1 Like
10

It looks a bit messy but I got it working :sweat_smile:

CASE WHEN DD.UNIT_OF_MEASURE_NAME = 'Feet & Inches' THEN CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE) WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(FLOOR(PD.DISCRETE_DATA_VALUE / 12) AS VARCHAR)+'.' + CAST(FLOOR(CAST(ROUND(PD.DISCRETE_DATA_VALUE %12,0)AS VARCHAR))AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Centimeters' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE / 2.54 AS DECIMAL(10,2))/12 AS VARCHAR(10)) WHEN DD.UNIT_OF_MEASURE_NAME = 'Inches' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Ounces' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE/16 AS DECIMAL(10,2))AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Kilograms' THEN CAST(CAST(PD.DISCRETE_DATA_VALUE * 2.2 AS DECIMAL(10,2))AS VARCHAR) WHEN DD.UNIT_OF_MEASURE_NAME = 'Pounds & Ounces' THEN CAST(CONVERT(DECIMAL(10,2),PD.DISCRETE_DATA_VALUE)AS VARCHAR) END AS FINAL

Thanks for the advice everyone!

Cheers!

M