Feeds:
Posts
Comments

Posts Tagged ‘Conversion Function’

SQL SERVER – How to get only the numbers after the decimal?

Posted in Solutions, SQL SERVER, tagged , , , , , on December 16, 2013| 2 Comments »

I was working on an SQL server tool and got stuck in a place where I had to split the the numbers into two parts, first should be before the decimal part and second should be after the decimal part. In my earlier articles, I already wrote how to get the numbers before the decimal. In this article, I will share, how to get the numbers after the decimals.

Let me create a sample, before proceeding with 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.90)
GO

Given below are the solutions.

Solution 1 : (Without any function)

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

number after decimal.1.1

Solution 2 : (Using CAST & ABS function)

USE Tempdb
GO
SELECT [Col_ID], [Col_Decimal]
, ABS([Col_Decimal]) - CAST(ABS([Col_Decimal]) AS INT)
As [Col_After_decimal]
FROM tbl_sample
GO
--OUPUT

number after decimal.1.1

Read Full Post »

SQL SERVER – How do I get only the numbers before the decimal?

Posted in Solutions, SQL SERVER, tagged , , , , , on October 3, 2013| 1 Comment »

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 »

SQL SERVER 2012 -Conversion Function -TRY_CAST

Posted in Functions, SQL SERVER, tagged , , , on August 28, 2012| Leave a Comment »

In my previous post, I discussed about TRY_CONVERT. Today, we will discuss one more excellent conversion function of SQL server namely “TRY_CAST”. Before SQL server 2012, we use “Cast” function very frequently. whenever we need to cast expression from one data type to another data type, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to cast it into incompatible data type. It will generate an error. TRY_CAST is the solution of this problem.
For Example :

Select Cast ('test' as int)
--Result

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.

TRY_CAST requires three parameters expression, data type & Length. Expression and Data type are mandatory but the Length is optional.
Lets discuss this function syntax, purpose and examples in detail.

Syntax :

TRY_CAST ( expression AS data_type [ ( length ) ] )

Purpose :
This function cast expression from one data type to another data type and if it fails to cast, it give result as NULL.

Example-1 : TRY_CAST – Without Error

Select TRY_Cast(123 as int) as [Result]
--Result
123

Example-2 : TRY_CAST – With Error
If TRY_CAST fails, it will return NULL value.

Select TRY_Cast ('test' as int) as [Result]
--Result
NULL

Example-3 : TRY_Cast WITH IIF STATEMENT

In this example , we will try to convert a valid and invalid expression to Integer by using IIF statement.

Select IIF(Try_Cast('test' as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Invalid Integer

Select IIF(Try_Cast(2 as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Valid Integer

Example-4 : TRY_Cast WITH CASE STATEMENT
In this example , we will try to cast a valid and invalid expression to Integer by using case statement.

Select (Case When Try_Cast(123 as int  ) is NULL Then 'Invalid Integer' else 'Valid Integer' end)
--Result
Valid Integer

Select (Case When Try_Cast(123.45 as int) is NULL Then 'Invalid Integer' else  'Valid Integer' end)
--Result
Invalid Integer

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CONVERT

Posted in Functions, SQL SERVER, tagged , , , , on August 27, 2012| 7 Comments »

In SQL server, we use “Convert” function very frequently.Whenever, we need to convert one expression data type to another, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to convert it. It will generate an error and can impact you application.
For Example :

Select Convert (datetime2,'2012-08-32')
--Result

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

To resolve this issue, Microsoft has introduced a new function in SQL SERVER 2012, namely “TRY_CONVERT”.
It requires three parameters data type, expression & style. Data type and expression are mandatory but the style is optional.
Lets discuss this function syntax , purpose and examples in detail.

TRY_CONVERT

Syntax :

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Purpose :
This function converts expression from one data type to another data type and if it fails to convert it will return a NULL value as a results.

Example-1 : TRY_CONVERT – Passed

Select TRY_Convert(datetime,'2012-09-30',101) as [Result]
--Result
2012-09-30 00:00:00.000

Example-2 : TRY_CONVERT – Failed
If try _convert Failed,  it will return NULL value.
Lets say we don’t have 31 days in September. Now, try to convert it

Select TRY_Convert(datetime,'2012-09-31',101) as [Result]
--Result
NULL

Example-3 : TRY_CONVERT WITH IIF STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select IIF(Try_Convert(int, 'test') is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Invalid Integer

Select IIF(Try_Convert(int, 2) is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Valid Integer

Example-4 : TRY_CONVERT WITH CASE STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select (Case When Try_Convert(datetime2,'2012-08-22') is NULL Then 'Invalid Date format' else 'Valid Date format' end) as [Result]
--Result
Valid Date format

Select (Case When Try_Convert(datetime2,'2012-08-32') is NULL Then 'Invalid Date format' else  'Valid Date format' end) as [Result]
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_PARSE()

Posted in Functions, SQL SERVER, tagged , , on August 27, 2012| 1 Comment »

In my previous post, I discussed about PARSE function. In this function, if the data type is not compatible and could not be parsed then this function will generate an error. Now, we have the solution for it namely “TRY_PARSE”. SQL SERVER has introduced this (TRY_PARSE) built in conversion function in 2012. This function is very useful because most of the time due to the incorrect data , your system may generate errors. And if you use this function it will return NULL in case of error.
Lets discuss its syntax , purpose and examples in detail.

TRY_PARSE

Syntax :

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function tries to parse string value (nvarchar(4000) data type) to datetime or numeric data types. But if it fails to parse, it will return NULL value. We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : TRY_PARSE – Successful 

SELECT TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US')
--Result
2012-08-22 00:00:00.0000000

Example-2 : TRY_PARSE – Unsuccessful
If the parsing will be unsuccessful, it will return NULL value.

SELECT TRY_PARSE('datetime2' AS datetime2 USING 'en-US')
--Result
NULL

Example-3 : TRY_PARSE – Real World Example with culture keyword
In this example, we will try to parse a valid and invalid date.

Select IIF(TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Example-4 : TRY_PARSE – Real World Example with Language keyword
In this example, we will try to parse a valid and invalid date with the language keyword instead of culture.

Set Language 'English'
Select IIF(TRY_PARSE('2012-08-22' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function – PARSE ()

Posted in Functions, SQL SERVER, tagged , , on August 26, 2012| 2 Comments »

SQL SERVER 2012 has a new built in function namely “PARSE”. Lets discuss its syntax , purpose and examples in detail.

PARSE

Syntax :

PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function converts the string value (nvarchar(4000) data type) to datetime or numeric data types.
We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : PARSE -Varchar to Numeric

SELECT PARSE('123.45' AS NUMERIC(18,2)) AS Output
--Result
123.45

Example-2 : PARSE – Varchar to Int
Lets use the same example of numeric and cast it to integer.

SELECT PARSE('123.45' AS INT) AS Output
--Result

Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘123.45’ into data type int using culture .
Note : The above line of code will generate error because whatever we want to parse should be compatible with the parse data type.

Now, lets do the same without scale.

SELECT PARSE('123' AS INT) AS Output
--Result
123

Example-3 : PARSE – Varchar to Datetime with Default culture.

Select Parse('Aug 26 2012' as datetime)
Select Parse('08/26/2012' as datetime )
Select Parse('2012.08.26' as datetime)
Select Parse('26 Aug 2012' as datetime)
Select Parse('Aug 26, 2012' as datetime)
Select Parse('08-26-2012' as datetime)
Select Parse('2012/08/26' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Example-4 : PARSE – Varchar to Datetime with culture

Select Parse('26/08/2012' as datetime using 'fr-FR')  --French
Select Parse('26.08.2012' as datetime using 'de-DE')  --German
Select Parse('26-08-2012' as datetime using 'it-IT')  --Italian
--Result of the above statements
2012-08-26 00:00:00.000

Example-5 : PARSE – Varchar to Currency with culture

Select Parse('$123.45' as money using 'en-US')
Select Parse('€123,45' AS money using 'de-DE')
--Result of the above statements
123.45

Example-6 : PARSE – Language Setting Instead of Culture

SET LANGUAGE 'French'
Select Parse('26/08/2012' as datetime)

SET LANGUAGE 'German'
Select Parse('26.08.2012' AS datetime2) AS Result

SET LANGUAGE 'Italian'
Select Parse('26-08-2012' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Error :
If the string value is invalid to convert it into date time, numeric & any specific culture format, then it will generate an error.

Reference : MSDN

Read Full Post »