How to find out datatype of an expression?

Well. You have an expression that produces a result and you want to know the datatype of it. How will you do it?

For example you have the following expression 10+’20’

select 10+'20'

The result is 30

But you do not know if the result is datatype of INT or VARCHAR

You can find out the resultant datatype in two methods

1 Create a table based on expression

select 10+'20' as data into #t

Now see the structure of the column and find the datatype

EXEC TEMPDB..SP_COLUMNS '#t'

The result is

dt1

2 Use SQL_VARIANT_PROPERTY system function

SELECT SQL_VARIANT_PROPERTY(10+'20','basetype') as datatype

The result is

datatype
-----------------
INT

You can also use a variable in place of an expression to find out it’s datatype

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s