Feeds:
Posts
Comments

Archive for October, 2013

I came across this query today, when I was generating some reports and I do not want to show the decimals in the report. I did some research and came up with multiple solutions. Before proceeding with the solution, let me create a sample to demonstrate the solution.

Sample :

USE Tempdb
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
 [Col_ID] INT,
 [Col_Decimal] decimal(18,4)
)
GO
INSERT INTO tbl_sample VALUES (1,12345.9876)
INSERT INTO tbl_sample VALUES (2,-12345.9876)
INSERT INTO tbl_sample VALUES (3,123.45)
INSERT INTO tbl_sample VALUES (4,12.00)
GO

Given below are the solutions.

Solution 1 : (Without any function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, [Col_Decimal] - ([Col_Decimal]%1) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.1

Solution 2 : (Using ROUND function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ROUND([Col_Decimal],0,1) As [Col_Number]
FROM tbl_sample
GO
--OUPUT

get the number before decimals.1.2

Solution 3 : (Using CAST function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, CAST([Col_Decimal] AS INT) As [Col_Number]
FROM tbl_sample
GO
--OUTPUT

get the number before decimals.1.3

Read Full Post »

« Newer Posts