You can run

"describe tablename;" to get the datatypes of a column.

I ran a few quick examples and found the following to work, I think you are probably close but just some formatting issues.

Explore some of these examples and see if you can find your error.

Remember to delete any tables you create if you are on a shared workspace.

Create a table with a decimal as a column named id.

create table temp6 as (Select 20140419.00 id);

describe temp6;

-- to show it is indeed a decimal

Select from it and note that sql deals with int to float

%sql select * from temp6 where id = 20140419;

Also casts strings for you

%sql select * from temp6 where id = "20140419";

Do the same with ints using

create table temp7 as (Select 20140419 id);

This last example might get you close,

select * from temp6 where id < replace(cast(current_date() as String), '-');

Break that down by running

select current_date()

Then cast to string

select cast(current_date() as String);

Then drop the dashes

select replace(cast(current_date() as String), '-')

Then use that in your where clause, but modify as needed to suit your logic.

Note mine worked when I compared an int or a decimal to a string, sparkSQL casts as appropriate.