Feeds:
Posts
Comments

Posts Tagged ‘TRY_CONVERT’

SQL SERVER – Multiple ways to convert Datetime to Varchar

Posted in Solutions, SQL SERVER, tagged , , , , , , on February 4, 2014| Leave a Comment »

Data presentation is one of the aspects that is much more important when you need to present a report to end user. Usually, I recommend that you should do it at the presentation layer (front end). However, sometimes we need to do all the formatting at database level itself and just present it at presentation level. One of the most frequently formattings we usually come across is datetime data type formatting and to format datetime we need to convert it into varchar data type.

Given below are the solutions.

Solution 1 :
In this solution, we need to use the Traditional method using CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.1

Solution 2 :
In this solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, TRY_CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Try_Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.2

Solution 3 :
In this solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function.
I usually recommend this method because it gives you variety for formatting.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, FORMAT(OrderDate,'dd MMM yyyy')
AS [OrderDate Using Format]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

datetimetovarchar.1.3

Read Full Post »

SQL SERVER 2012– Convert Text to Numbers

Posted in Solutions, SQL SERVER, tagged , , , , , , , on October 14, 2012| Leave a Comment »

In this article, we will convert text to number in multiple versions of SQL Server and will see the difference. I will use four different Data conversion functions (Convert, Cast, Try_Convert &  Try_Cast) to convert Text to Number.

Let me explain this with simple examples.

In SQL Server 2005/2008 :

Example 1 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 2 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------

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

In SQL Server 2012 :

Example 3 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 4 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------
NULL

(1 row(s) affected)

Cast Text to Integer
--------------------
NULL

(1 row(s) affected)

Conclusion :
If we compare Examples 1 & 3, we can easily find out that there is no difference, but we can see that Examples 2 and 4 having much difference because Cast & Convert generates an error if there is a compatibility issue with data type, but Try_Convert & Try_Cast (SQL Server 2012) will return NULL instead of error.

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 »