CODEWITHSUNDEEP
sqlt-sqlsql-server-2005type-conversion
Lemex
Lemex

Reputation: 3804

Cast/Convert Text-Decimal

I know this is bad practice but its the only way i can do it due to the data migration column requirements.

I am looking to round up(CEIL) or round down (FLOOR) my value in a text feild.

I know for a fact they are all either integers or decimals so no worry about bad data there.

UPDATE [dbo].[T01]
SET [2 37] =    CAST(
             FLOOR(CONVERT(decimal(10,5),(CAST([2 37] AS varchar(10)))))
             AS text )
WHERE [2 37] LIKE '%.%'

My first attempt was:

UPDATE [dbo].[Tomcat 19032013 01]
SET [2 37] = FLOOR([2 37])
WHERE [2 37] LIKE '%.%'

Long story short i want to be able to floor or ceil that data in my text field!

I am using SQL 2005 - Developer Edition

Upvotes: 0

Views: 426

Answers (2)

HLGEM
HLGEM

Reputation: 96640

You should add columns to do the conversion.

DECLARE @T01 TABLE([2 37] text, [2 37 Floor] int, [2 37 Ceiling] int)
INSERT @T01 ([2 37]) VALUES('1.5')
INSERT @T01 ([2 37]) VALUES('4.1')
INSERT @T01 ([2 37]) VALUES('5.9')

update t
set [2 37 Floor]=floor(CONVERT(decimal(8,2),(CAST([2 37] AS varchar(10))))  )
,  [2 37 Ceiling] =ceiling(CONVERT(decimal(8,2),(CAST([2 37] AS varchar(10)))))
from @T01 t
WHERE [2 37] LIKE '%.%'
select * from @T01

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44346

I am assuming you want to round off the value to nearest integer

Rounding down

UPDATE [dbo].[T01]
SET [2 37] =
CAST(CAST(FLOOR(CONVERT(decimal(10,5),(CAST([2 37] AS varchar(10))))) as VARCHAR(10))
AS text )
WHERE [2 37] LIKE '%.%'

Rounding up

UPDATE [dbo].[T01]
SET [2 37] =    CAST(
CAST(CEILING(
CONVERT(decimal(10,5),(CAST([2 37] AS varchar(10))))) as VARCHAR(10))
AS text )
WHERE [2 37] LIKE '%.%'

Upvotes: 2

Related Questions