Feeds:
Posts
Comments

Archive for the ‘Data Conversion’ Category

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_ConvertTry_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.

Advertisements

Read Full Post »

Date data type plays an important role in the database and when its subject comes, we cannot skip its formatting as well. Today, we will discuss how to do formatting of date data type without using a single function like Convert, Cast, Substring, DateName, Replace etc.
In SQL Server 2012, we can use one functionFORMAT to format date instead of multiple functions.
Let me explain it with simple examples to convert date into different formats using Format:

Declare @Date as Date =Getdate()
Select @Date --2012-10-13

Select Format(@Date,'yy.MM.dd') as [yy.MM.dd] --12.10.13

Select Format(@Date,'yyyy.MM.dd') as [yyyy.MM.dd] --2012.10.13

Select Format(@Date,'dd.MM.yyyy') as [dd.MM.yyyy] --13.10.2012

Select Format(@Date,'MM/dd/yy') as [MM/dd/yy] --10/13/12

Select Format(@Date,'MM/dd/yyyy') as [MM/dd/yyyy] --10/13/2012

Select Format(@Date,'dd/MM/yyyy') as [dd/MM/yyyy] --13/10/2012

Select Format(@Date,'dd-MM-yyyy') as [dd-MM-yyyy] --13-10-2012

Select Format(@Date,'dd MMM yyyy') as [dd MMM yyyy] --13 Oct 2012

Select Format(@Date,'MMM dd, yyyy') as [MMM dd, yyyy] --Oct 13, 2012

Select Format(@Date,'MM-dd-yy') as [MM-dd-yy] --10-13-12

Select Format(@Date,'MM-dd-yyyy') as [MM-dd-yyyy] --10-13-2012

Select Format(@Date,'yy/MM/dd') as [yy/MM/dd] --12/10/13

Select Format(@Date,'yyyy/MM/dd') as [yyyy/MM/dd] --2012/10/13

Select Format(@Date,'yyMMdd') as [yyMMdd] --121013

Select Format(@Date,'yyyyMMdd') as [yyyyMMdd] --20121013

Select Format(@Date,'yy-MM-dd') as [yy-MM-dd] --12-10-13

Select Format(@Date,'yyyy-MM-dd') as [yyyy-MM-dd] --2012-10-13

Select Format(@Date,'MM/yy') as [MM/yy] --10/12

Select Format(@Date,'MM/yyyy') as [MM/yyyy] --10/2012

Select Format(@Date,'yy/MM') as [yy/MM] --12/10

Select Format(@Date,'yyyy/MM') as [yyyy/MM] --2012/10

Select Format(@Date,'MMMM dd,yyyy') as [MMMM dd,yyyy] --October 13,2012

Select Format(@Date,'MMM yyyy') as [MMM yyyy] --Oct 2012

Select Format(@Date,'MMMM yyyy') as [MMMM yyyy] --October 2012

Select Format(@Date,'dd MMMM') as [dd MMMM] --13 October

Select Format(@Date,'MMMM dd') as [MMMM dd] --October 13

Select Format(@Date,'dd MMMM yy') as [dd MMMM yy] --13 October 12

Select Format(@Date,'dd MMMM yyyy') as [dd MMMM yyyy] --13 October 2012

Select Format(@Date,'MM-yy') as [MM-yy] --10-12

Select Format(@Date,'MM-yyyy') as [MM-yyyy] --10-2012

Select Format(@Date,'yy-MM') as [yy-MM] --12-10

Select Format(@Date,'yyyy-MM') as [yyyy-MM] --2012-10

Select Format(@Date,'MMddyy') as [MMddyy] --131012

Select Format(@Date,'MMddyyyy') as [MMddyyyy] --10132012

Select Format(@Date,'ddMMyy') as [ddMMyy] --131012

Select Format(@Date,'ddMMyyyy') as [ddMMyyyy] --13102012

Select Format(@Date,'MMM-yy') as [MMM-yy] --Oct-12

Select Format(@Date,'MMM-yyyy') as [MMM-yyyy] --Oct-2012

Select Format(@Date,'dd-MMM-yy') as [dd-MMM-yy] --13-Oct-12

Select Format(@Date,'dd-MMM-yyyy') as [dd-MMM-yyyy] --13-Oct-2012

Read Full Post »

I received a question from a blog reader “how to convert varbinary to numeric & vice versa & How SQL Server keeps the structure of numeric data type in varbinary format ?”

First lets take an example to convert from varbinary to numeric & vice versa”

Example :

--Convert from varbinary to numeric
Declare @varbinary2 as varbinary(max)
Set @varbinary2=0x08040001B471BC00
Select Convert(numeric(8,4),@varbinary2) as [Varbinary to Numeric]

--Convert from numeric to varbinary
Declare @Numeric2 as numeric(8,4)
Set @Numeric2 =1234.9876
Select Convert(varbinary(max),@Numeric2) as [Numeric to Varbinary]
--RESULT

Varbinary to Numeric
—————————————
1234.9876

(1 row(s) affected)

Numeric to Varbinary
—————————————
0x08040001B471BC00

(1 row(s) affected)

Explanation :
It is a simple conversion, but one thing you need to remember is, whenever you convert from varbinary to numeric, you must be exact in the Precision & Scale. If Precision & Scale are wrong, it will give you wrong result. But the question is how ascertain that the Precision & Scale are in varbinary format ?

Lets take an example from above and first get the Precision & Scale and then convert it into numeric data type.

--Convert from varbinary to numeric
Declare @varbinary1 as varbinary(max)
Set @varbinary1=0x08040001B471BC00

Select Convert(int,0x08) as [Precision]
Select Convert(int,0x04) as [Scale]

Select @varbinary1 as [Varbinary]
,Convert(numeric(18,4),@varbinary1) as [Varbinary to Numeric]
--RESULT

Precision
———–
8

(1 row(s) affected)

Scale
———–
4

(1 row(s) affected)

Varbinary | Varbinary to Numeric
———————– | —————————————
0x08040001B471BC00 | 1234.9876

(1 row(s) affected)

Given below is the screen image that will show how SQL keeps numeric data type in varbinary format.

Read Full Post »