Convert text to string

[iluvwitter]

I have the following sql and trying to convert the hard code fields (tran_type and flag) to integer. These are currently resulting as text fields. Can someone advise how? Tried to put a cast around it, but it keeps giving me a syntax error.

select
'0' as 'tran_type',
'1' as 'flag',
tradedate
from tableA

Thanks!

 

[markros]

select cast(0 as int) as 'Tran_Type', cast(1 as int) as 'Flag', TradeDate from TableA

 

[gmmastros]

You could also simply remove the single-quotes.

select
0 as 'tran_type',
1 as 'flag',
tradedate
from tableA

When you hard code data in a query, SQL will determine the data type based on syntax. When you use a single-quote, SQL will interpret this as a varchar. Without the single quotes, SQL will interpret as int.

Take a look at the following sample code to see what I mean.

SELECT SQL_VARIANT_PROPERTY('1', 'BaseType')       -- varchar
SELECT SQL_VARIANT_PROPERTY(1, 'BaseType')         -- int
SELECT SQL_VARIANT_PROPERTY(1.234, 'BaseType')     -- numeric
SELECT SQL_VARIANT_PROPERTY('1/1/2010', 'BaseType')-- varchar (not datetime)
SELECT SQL_VARIANT_PROPERTY(0x00000001, 'BaseType')-- varbinary

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom