Sure. With the understanding that such data should never be stored in a table and that it should be normalized as a real DATETIME column and a separate column for the "time of day slot" indicator, here's how to solve this problem in a SARGable fashion. For those that don't know, "SARGable" has come to basically mean "can do an Index Seek if the correct supporting index is available".

--=============================================================================

-- Create a larger test table with the appropriate index

-- Only adding the appropriate index is a part of the solution

--=============================================================================

--DROP TABLE dbo.#DateTest

GO

--===== Create the table, as before

CREATE TABLE dbo.#DateTest

(

FromDate varchar(9)

,ToDate varchar(9)

)

;

--===== Insert the original 4 rows in the test data

INSERT INTO dbo.#DateTest

SELECT '201401221','201401292' UNION ALL

SELECT '201401092','201401161' UNION ALL

SELECT NULL ,'201402282' UNION ALL

SELECT '201401152',NULL

;

GO

--===== Insert another 16380 similar rows

INSERT INTO #DateTest

SELECT * FROM #DateTest

GO 12

--===== Add the expected index

CREATE INDEX IX_#DateTest

ON #DateTest (FromDate,ToDate)

;

--=============================================================================

-- Demonstrate the the current solution will NOT do an Index Seek

-- and a method that will. The Index Seek is followed by a nice

-- high performance range scan

--=============================================================================

--===== Setup the variable for @Today to make testing easy

DECLARE @Today DATETIME

SELECT @Today = GETDATE() --Or whatever

;

--===== This CANNOT do an Index Seek because of the formulas

-- on the FromDate and ToDate columns (non-SARGable).

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

;

--===== This DOES do an Index Seek because there are no forumulas

-- on the table columns in the WHERE clause (SARGable).

SELECT FromDate = FromDate -- ISNULL(FromDate,'19000101')

,ToDate = ToDate -- ISNULL(ToDate ,'99991231')

FROM dbo.#DateTest

WHERE (FromDate <= CONVERT(CHAR(8),@Today ,112)+'9' OR FromDate IS NULL)

AND (ToDate >= CONVERT(CHAR(8),@Today ,112)+'0' OR ToDate IS NULL)

;

Notice the neither FromDate or ToDate is contained in a formula.