As the first reply states, it would be easier to this using date or datetime data types, but assuming that you are locked in to using the varchar data type, you could do the following.

I've included the table creation and inserting sample data steps for clarity.

CREATE TABLE dbo.#DateTest

(

FromDate varchar(9)

,ToDate varchar(9)

)

Insert some sample data:

INSERT INTO dbo.#DateTest VALUES ('201401221','201401292'),('201401092','201401161'),(NULL,'201402282'),('201401152',NULL)

Declare a variable to hold today's date in integer format:

DECLARE @Today int = CAST(CONVERT(varchar,getdate(),112) AS int)

In your post you've got your condition round the wrong way...unless you system date is set to 19000101 or earlier!

SELECT ISNULL(CAST(SUBSTRING(FromDate,1,8) AS int),19000101) AS FromDate, ISNULL(CAST(SUBSTRING(ToDate,1,8) as int),99991231) AS ToDate FROM dbo.#DateTest

WHERE ISNULL(SUBSTRING(FromDate,1,8),19000101) <= @Today

AND ISNULL(SUBSTRING(ToDate,1,8),99991231) > @Today

Result:

FromDateToDate

2014012220140129

1900010120140228

2014011599991231

EDIT: Removed an unnecessary CAST from the WHERE clause.

Regards

Lempster