Feeds:
Posts
Comments

Archive for the ‘Conversion Function’ Category

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 »

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 »

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 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 »