i'm new in sql language, and i would appreciate your help. I want to pass a variable (i.e. a column) to a predefined function like "convert" or "hextobigint".
select hextobigint('14c9d0742dc')
returns 1428563641052
meanwhile the below code doesn't gets executed ()
begin
declare @st varchar(16);
set @st=dc.table.START_TIME;
select hextobigint('dc.table.START_TIME') FROM dc.table;
end
Error
Correlation name "table" not found.
Also for the function returns the correct value:
select convert (bigint,0x14c9d0742dc)
but
select convert (bigint,14c9d0742dc)
doesn't
Syntac error 'c9d0742dc' on line 1.
also
select convert(bigint,dc.table.START_TIME) FROM dc.table
returns only null because the format of the column is like "14c9d0742dc".
The scope is to pass variable (column) to these functions "hextobigint" and "convert". Could you please help?
You can not convert an alphanumeric to a bigint with the convert statement above. So you would need to change the convert to first convert Hex to Int. But then you do not need the convert statement as the following two lines return the same value. So just use the second one.
select convert (bigint,hextobigint('0x14c9d0742dc'));
select hextobigint('0x14c9d0742dc');
Regarding your first problem the syntax is wrong but I am not sure what you are trying to get back. If you want to convert the column START_TIME from a table called "yourTableName" then you could do this.
begin
select hextobigint(START_TIME) FROM table_raw;
end
Alternatively try this version with dynamic SQL based on the constraint in your comment below.
begin
declare @sql varchar(200);
EXECUTE IMMEDIATE WITH RESULT SET ON 'select hextobigint(START_TIME) FROM table_raw;';
end